[Oracle JSON 함수 정복] JSON_ARRAYAGG 함수 완벽 가이드 ｜ 여러 행을 JSON 배열로 묶기

 

오라클에서 JSON 데이터를 다루는 기능은 버전이 거듭될수록 강력해지고 있습니다. 특히 오라클 12c Release 2부터 도입된 JSON_ARRAYAGG 함수는 여러 행의 데이터를 하나의 JSON 배열로 묶어주는 매우 유용한 함수입니다. 본 포스팅에서는 JSON_ARRAYAGG 함수의 기본 개념부터 실제 예제, 다양한 옵션 사용법까지 상세하게 설명하여 실무에 바로 적용할 수 있도록 돕겠습니다.

---

🔎 JSON_ARRAYAGG 함수란?

JSON_ARRAYAGG 함수는 다수의 행(row)에서 특정 컬럼 값을 하나의 JSON 배열 형태로 변환해주는 집계 함수입니다. 예를 들어 이름이 10개 있는 테이블에서 이 함수로 ename 컬럼을 묶으면 ["JONES", "CLARK", "BLAKE"]와 같은 JSON 배열이 반환됩니다.

이 함수는 다음과 같은 상황에서 유용합니다:

• 다수의 레코드를 프론트엔드로 JSON으로 반환할 때
• REST API에서 JSON 응답을 직접 Oracle DB에서 만들고자 할 때
• 기존의 LISTAGG, WM_CONCAT 함수로는 처리할 수 없는 복합 데이터 구조가 필요할 때

✅ 오라클 12c R2 (12.2) 이상 버전에서 사용 가능합니다.

---

📌 JSON_ARRAYAGG 함수 문법

JSON_ARRAYAGG(
  expr [FORMAT JSON]
  [ORDER BY column [ASC | DESC]]
  [NULL ON NULL | ABSENT ON NULL]
  [RETURNING data_type]
)

옵션 설명

expr	배열로 묶을 컬럼 또는 표현식
FORMAT JSON	expr이 이미 JSON 형식이라면 지정
ORDER BY	배열 요소 정렬
NULL ON NULL	NULL 값 포함 (기본값)
ABSENT ON NULL	NULL 값 생략
RETURNING	결과 데이터 타입 지정 (VARCHAR2, CLOB, BLOB)

---

🧪 기본 사용법 예제

WITH emp AS (
    SELECT 7698 empno, 'BLAKE' ename FROM dual UNION ALL
    SELECT 7782 empno, 'CLARK' ename FROM dual UNION ALL
    SELECT 7566 empno, 'JONES' ename FROM dual
)
SELECT JSON_ARRAYAGG(ename) AS json_data
  FROM emp;

결과

["BLAKE","CLARK","JONES"]

단순한 컬럼 집계를 배열로 반환하며, ORDER BY 절이 없기 때문에 결과 순서는 보장되지 않습니다.

---

🔄 ORDER BY를 활용한 정렬된 배열 반환

SELECT JSON_ARRAYAGG(ename ORDER BY ename DESC) AS json_data
  FROM emp;

결과

["JONES", "CLARK", "BLAKE"]

ORDER BY를 사용하면 원하는 기준으로 JSON 배열 내부를 정렬할 수 있습니다.

---

🧱 JSON_OBJECT와 함께 사용 (JSON 객체 배열)

SELECT JSON_ARRAYAGG(
           JSON_OBJECT(
               KEY 'EMPNO' VALUE empno,
               KEY 'ENAME' VALUE ename
           )
       ) AS json_data
  FROM emp;

결과

[
  {"EMPNO":7698,"ENAME":"BLAKE"},
  {"EMPNO":7782,"ENAME":"CLARK"},
  {"EMPNO":7566,"ENAME":"JONES"}
]

다중 필드를 포함하는 JSON 객체를 배열로 묶을 수 있어, 실제 응용에서는 이 방식이 가장 많이 사용됩니다.

---

📁 FORMAT JSON 옵션 사용법

expr이 이미 JSON 형식 문자열이라면 FORMAT JSON 옵션을 지정해야 정상적으로 배열로 묶입니다.

WITH emp_json AS (
    SELECT '{"EMPNO":7698,"ENAME":"BLAKE"}' json_data FROM dual UNION ALL
    SELECT '{"EMPNO":7782,"ENAME":"CLARK"}' json_data FROM dual UNION ALL
    SELECT '{"EMPNO":7566,"ENAME":"JONES"}' json_data FROM dual
)

SELECT JSON_ARRAYAGG(json_data FORMAT JSON) AS json_array
  FROM emp_json;

결과

[
  {"EMPNO":7698,"ENAME":"BLAKE"},
  {"EMPNO":7782,"ENAME":"CLARK"},
  {"EMPNO":7566,"ENAME":"JONES"}
]

⚠️ FORMAT JSON STRICT 를 추가하면 JSON 구조 검증을 강화할 수 있습니다.

---

⚖️ NULL 처리 옵션 (NULL ON NULL vs ABSENT ON NULL)

WITH emp AS (
    SELECT 7698 empno, 'BLAKE' ename, 2850 sal FROM dual UNION ALL
    SELECT 7782 empno, 'CLARK' ename, 2450 sal FROM dual UNION ALL
    SELECT 7566 empno, 'JONES' ename, NULL sal FROM dual
)

SELECT JSON_ARRAYAGG(sal)                AS result1,
       JSON_ARRAYAGG(sal NULL ON NULL)   AS result2,
       JSON_ARRAYAGG(sal ABSENT ON NULL) AS result3
  FROM emp;

옵션 설명 결과

기본	NULL 포함	[2850, 2450, null]
NULL ON NULL	명시적으로 NULL 포함	[2850, 2450, null]
ABSENT ON NULL	NULL 생략	[2850, 2450]

📌 NULL ON NULL과 ABSENT ON NULL 옵션은 Oracle 19c 이상에서 안정적으로 지원됩니다.

---

💾 RETURNING 옵션 (데이터 타입 지정)

기본적으로 JSON_ARRAYAGG의 결과는 VARCHAR2(4000) 형식으로 반환됩니다. 그러나 결과가 4000자를 초과할 경우 CLOB 또는 BLOB을 지정해야 오류 없이 사용할 수 있습니다.

SELECT JSON_ARRAYAGG(ename)                          AS result1,
       JSON_ARRAYAGG(ename RETURNING VARCHAR2(4000)) AS result2,
       JSON_ARRAYAGG(ename RETURNING CLOB)           AS result3,
       JSON_ARRAYAGG(ename RETURNING BLOB)           AS result4
  FROM emp;

RETURNING 옵션 설명

VARCHAR2(4000)	기본 반환 타입, 4000자 제한
CLOB	문자형 대용량 데이터 지원
BLOB	바이너리 대용량 데이터

---

✅ 실무 예제 3가지

예제 ① 고객 주문 내역을 JSON 배열로 반환

SELECT customer_id,
       JSON_ARRAYAGG(JSON_OBJECT(KEY 'ORDER_ID' VALUE order_id, KEY 'AMOUNT' VALUE amount)) AS orders
  FROM order_table
 GROUP BY customer_id;

예제 ② REST API를 위한 JSON 응답 생성

SELECT JSON_OBJECT(
           KEY 'status' VALUE 'success',
           KEY 'data' VALUE JSON_ARRAYAGG(JSON_OBJECT(KEY 'id' VALUE empno, KEY 'name' VALUE ename))
       ) AS json_response
  FROM emp;

예제 ③ 복합 조건 정렬 배열 생성

SELECT JSON_ARRAYAGG(
           JSON_OBJECT(KEY 'ENAME' VALUE ename, KEY 'SAL' VALUE sal)
           ORDER BY sal DESC
       ) AS salary_rank
  FROM emp;

---

📝 마무리 정리

기능 설명

JSON 배열 반환	다중 행을 JSON 배열로 반환
정렬 지원	ORDER BY로 순서 제어
복합 객체 표현	JSON_OBJECT와 함께 사용
널 처리 유연성	NULL ON NULL, ABSENT ON NULL 선택 가능
대용량 지원	RETURNING CLOB, BLOB 지원

---

🧭 결론

오라클의 JSON_ARRAYAGG 함수는 SQL 레벨에서 JSON 배열을 쉽게 생성할 수 있게 해주는 매우 강력한 도구입니다. REST API, 프론트엔드 연동, 데이터 마이그레이션 작업 등 다양한 실무 시나리오에서 유용하게 사용할 수 있습니다.

복잡한 JSON 구조도 SQL 하나로 해결할 수 있는 만큼, 지금부터 실무에 적용해 보세요. 특히 JSON_OBJECT, ORDER BY, RETURNING 옵션 등을 함께 적절히 활용하면 다양한 요구 사항을 깔끔하게 처리할 수 있습니다.

---

#오라클JSON #JSON_ARRAYAGG #오라클SQL #JSON객체배열 #Oracle12c #Oracle19c #오라클집계함수 #CLOB #RESTAPI연동