[Oracle] 오라클 JSON_ARRAYAGG 함수 사용법

오라클 SQL에서 여러 행의 데이터를 하나의 배열로 반환하기 위해서는 JSON_ARRAYAGG 함수를 사용하면 된다. JSON_ARRAYAGG 함수는 ORDER BY를 사용하여 배열 요소를 정렬할 수 있으며, CLOB 형식으로 반환할 수 있기 때문에 대용량의 데이터를 하나의 문자열로 합쳐서 반환할 때도 사용할 수 있다. JSON_ARRAYAGG 함수는 오라클 12c R2부터 사용할 수 있다.

 

JSON_ARRAYAGG(   expr [FORMAT JSON]   -- 배열에 포함할 열이나 표현식 (JSON 데이터라면 FORMAT JSON 사용)   [ORDER BY column [ASC | DESC]]   -- 배열 요소를 정렬 (기본적으로 순서가 보장되지 않음)   [NULL ON NULL | ABSENT ON NULL]   -- NULL 값을 포함하거나 생략 (기본: NULL ON NULL)   [RETURNING data_type]  -- 결과 데이터 유형 지정 (VARCHAR2 | CLOB | BLOB) )

 

목차 JSON_ARRAYAGG 기본 사용법 JSON_ARRAYAGG 옵션 사용법

 

JSON_ARRAYAGG 기본 사용법

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

SELECT JSON_ARRAYAGG(ename) AS json_data
  FROM emp

 

 

JSON_ARRAYAGG 함수는 여러 행의 값을 하나의 JSON 배열로 반환한다.

함수의 사용법은 WM_CONCAT, XMLAGG, LISTAGG 함수와 유사하다고 생각하면 된다.

 

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

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

 

 

JSON_ARRAYAGG 함수는 ORDER BY 절을 사용하여 값을 정렬 후 JSON 배열로 반환할 수 있다.

 

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

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

 

 

JSON_OBJECT 함수와 함께 사용하면 테이블의 여러 행 데이터를 JSON 형식으로 변환해서, 하나의 JSON 배열로 반환할 수 있다.

 

JSON_ARRAYAGG 옵션 사용법

옵션	설명
FORMAT JSON	입력값이 JSON 형식임을 지정
ORDER BY column [ASC|DESC]	JSON 배열 요소를 정렬
NULL ON NULL	NULL 값을 JSON 배열에 포함
ABSENT ON NULL	NULL 값을 생략
RETURNING	결과 데이터 유형을 VARCHAR2, CLOB, 또는 BLOB로 반환

 

FORMAT JSON

SELECT JSON_ARRAYAGG('{"ENAME":"BLAKE"}')                    AS result1
     , JSON_ARRAYAGG('{"ENAME":"BLAKE"}' FORMAT JSON)        AS result2
     , JSON_ARRAYAGG('{"ENAME":"BLAKE"}' FORMAT JSON STRICT) AS result3     
  FROM dual

 

 

FORMAT JSON 옵션을 사용하면 JSON 형식의 문자열을 합칠 때 문자열을 JSON 형식으로 변환하여 반환한다.

STRICT 옵션을 추가로 사용하면 엄격한 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)
  FROM emp_json

 

 

여러 행의 JSON 문자열을 합칠 때 FORMAT JSON 옵션을 사용할 수 있다.

 

NULL ON NULL,  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 ON NULL 옵션을 사용하면 NULL 데이터도 포함하여 JSON 배열로 반환한다.

기본 값을 NULL은 생략하고 반환한다.

 

NULL ON NULL 옵션은 오라클 19c부터 정상적으로 작동한다. (이전 버전에서는 버그가 있는 듯...)

 

RETURNING

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(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

 

 

JSON_ARRAY 함수의 기본 반환 데이터 유형은 VARCHAR2(4000)이며, 

더 큰 값을 반환하기 위해서는 CLOB, BLOB 데이터 유형을 사용하면 된다.

 

[Oracle] 오라클 JSON_ARRAY 함수 사용법