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

오라클 SQL에서 여러 행의 데이터를 KEY, VALUE 형태로 하나의 JSON 객체로 합치기 위해서는 JSON_OBJECTAGG 함수를 사용하면 된다. JSON_OBJECTAGG 함수는 오라클 12c R2 이상에서 사용할 수 있다.

 

JSON_OBJECT(   [      { [KEY] key_expr VALUE val_expr }     -- JSON 객체의 "키:값" 쌍을 정의     [FORMAT JSON],     -- 값(val_expr)이 JSON 형식임을 명시. 유효하지 않은 JSON 형식이면 오류 발생     ...   ]   [ JSON_on_null_clause ]      -- NULL 처리 방식 지정 (NULL ON NULL 또는 ABSENT ON NULL)   [ JSON_returning_clause ]    -- 반환 데이터 유형 지정 (CLOB, VARCHAR2(size), BLOB)   [ STRICT ]                             -- JSON 형식 유효성 검사. 유효하지 않은 JSON 값이 있으면 오류 발생   [ WITH UNIQUE KEYS ]       -- JSON 객체의 키 중복 방지. 중복된 키가 있으면 오류 발생 )

 

 

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

 

JSON_OBJECTAGG 기본 사용법

WITH emp AS (
    SELECT 7788 empno, 'SCOTT' ename, 'ANALYST' job, 7566 sal FROM dual UNION ALL
    SELECT 7902 empno, 'FORD'  ename, 'ANALYST' job, 7566 sal FROM dual UNION ALL
    SELECT 7698 empno, 'BLAKE' ename, 'MANAGER' job, 2850 sal FROM dual UNION ALL
    SELECT 7782 empno, 'CLARK' ename, 'MANAGER' job, 2450 sal FROM dual UNION ALL
    SELECT 7566 empno, 'JONES' ename, 'MANAGER' job, 2975 sal FROM dual
)

SELECT JSON_OBJECTAGG(KEY ename VALUE sal) AS json_data
  FROM emp

 

 

JSON_OBJECTAGG 함수는 여러 행의 데이터를 KEY, VALUE로 지정하여 하나의 JSON 객체로 합칠 수 있다.

WM_CONCAT, XMLAGG, LISTAGG, JSON_ARRAYAGG 함수와 사용법이 비슷하다고 생각하면 된다.

 

WITH emp AS (
    SELECT 7788 empno, 'SCOTT' ename, 'ANALYST' job, 7566 sal FROM dual UNION ALL
    SELECT 7902 empno, 'FORD'  ename, 'ANALYST' job, 7566 sal FROM dual UNION ALL
    SELECT 7698 empno, 'BLAKE' ename, 'MANAGER' job, 2850 sal FROM dual UNION ALL
    SELECT 7782 empno, 'CLARK' ename, 'MANAGER' job, 2450 sal FROM dual UNION ALL
    SELECT 7566 empno, 'JONES' ename, 'MANAGER' job, 2975 sal FROM dual
)

SELECT job
     , JSON_OBJECTAGG(KEY ename VALUE sal) AS json_data
  FROM emp
 GROUP BY job

 

 

JSON_OBJECTAGG 함수는 그룹함수이기 때문에 GROUP BY 절과 함께 사용할 수 있다.

 

WITH emp AS (
    SELECT 7788 empno, 'SCOTT' ename, 'ANALYST' job, 7566 sal FROM dual UNION ALL
    SELECT 7902 empno, 'FORD'  ename, 'ANALYST' job, 7566 sal FROM dual UNION ALL
    SELECT 7698 empno, 'BLAKE' ename, 'MANAGER' job, 2850 sal FROM dual UNION ALL
    SELECT 7782 empno, 'CLARK' ename, 'MANAGER' job, 2450 sal FROM dual UNION ALL
    SELECT 7566 empno, 'JONES' ename, 'MANAGER' job, 2975 sal FROM dual
)

SELECT JSON_OBJECTAGG(
            KEY TO_CHAR(empno) 
            VALUE JSON_OBJECT(
                        KEY 'ENAME' VALUE ename,
                        KEY 'JOB'   VALUE job,
                        KEY 'SAL'   VALUE sal
                  )
       ) AS json_data
  FROM emp

 

 

JSON_OBJECTAGG 함수의 VALUE 절에 JSON_OBJECT 함수를 사용하여 JSON 객체를 입력할 수 있다.

 

JSON_OBJECTAGG 옵션 사용법

WITH emp AS (
    SELECT 7788 empno, '{"ENAME":"SCOTT"}' info_json FROM dual UNION ALL
    SELECT 7902 empno, '{"ENAME":"FORD"}'  info_json FROM dual UNION ALL
    SELECT 7698 empno, '{"ENAME":"BLAKE"}' info_json FROM dual UNION ALL
    SELECT 7782 empno, '{"ENAME":"CLARK"}' info_json FROM dual UNION ALL
    SELECT 7566 empno, '{"ENAME":"JONES"}' info_json FROM dual
)

SELECT JSON_OBJECTAGG(KEY TO_CHAR(empno) VALUE info_json)             AS result1
     , JSON_OBJECTAGG(KEY TO_CHAR(empno) VALUE info_json FORMAT JSON) AS result2
  FROM emp

 

 

JSON_OBJECTAGG 함수의 기본 값은 JSON 문자열은 문자열 그대로 반환하지만,

FORMAT JSON 옵션을 사용하면 JSON 문자열인 경우 JSON 객체로 반환한다. 

 

WITH emp AS (
    SELECT 7788 empno, 'SCOTT' ename, 'ANALYST' job, 7566 sal FROM dual UNION ALL
    SELECT 7902 empno, 'FORD'  ename, 'ANALYST' job, 7566 sal FROM dual UNION ALL
    SELECT 7698 empno, 'BLAKE' ename, 'MANAGER' job, 2850 sal FROM dual UNION ALL
    SELECT 7782 empno, 'CLARK' ename, 'MANAGER' job, 2450 sal FROM dual UNION ALL
    SELECT 7566 empno, 'JONES' ename, 'MANAGER' job, NULL sal FROM dual
)

SELECT JSON_OBJECTAGG(KEY ename VALUE sal)                AS result1
     , JSON_OBJECTAGG(KEY ename VALUE sal NULL ON NULL)   AS result2
     , JSON_OBJECTAGG(KEY ename VALUE sal ABSENT ON NULL) AS result3
  FROM emp

 

 

JSON_OBJECTAGG 함수의 NULL ON NULL, ABSENT ON NULL 옵션은 오라클 19c 이상에서 정상 작동한다.

 

오라클 18c 이하 : VALUE가 NULL인 경우 모두 생략

오라클 19c 이상 : 기본 값 NULL 포함, NULL ON NULL NULL 포함, ABSENT ON NULL NULL 생략

 

WITH emp AS (
    SELECT 7788 empno, 'SCOTT' ename, 'ANALYST' job, 7566 sal FROM dual UNION ALL
    SELECT 7902 empno, 'FORD'  ename, 'ANALYST' job, 7566 sal FROM dual UNION ALL
    SELECT 7698 empno, 'BLAKE' ename, 'MANAGER' job, 2850 sal FROM dual UNION ALL
    SELECT 7782 empno, 'CLARK' ename, 'MANAGER' job, 2450 sal FROM dual UNION ALL
    SELECT 7566 empno, 'JONES' ename, 'MANAGER' job, NULL sal FROM dual
)

SELECT JSON_OBJECTAGG(KEY ename VALUE sal)                          AS result1
     , JSON_OBJECTAGG(KEY ename VALUE sal RETURNING VARCHAR2(4000)) AS result2
     , JSON_OBJECTAGG(KEY ename VALUE sal RETURNING CLOB)           AS result3
     , JSON_OBJECTAGG(KEY ename VALUE sal RETURNING BLOB)           AS result3   
  FROM emp

 

 

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

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