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

오라클 SQL에서 JSON 객체를 만들기 위해서는 JSON_OBJECT 함수를 사용하면 된다. JSON_OBJECT 함수는 키 이름(KEY)과 값(VALUE)의 매핑을 유연하게 설정할 수 있어 단순한 데이터 변환뿐 아니라 복잡한 JSON 객체를 동적으로 생성하는 데에도 유용하게 사용할 수 있다. JSON_OBJECT 함수는 오라클 12c R2 이상에서 사용할 수 있다.

 

JSON_OBJECT(   [      {        [KEY] key_expr VALUE val_expr   -- JSON 객체의 "키:값" 쌍 지정       | expr [ ":" expr ]                             -- "키:값"을 표현식으로 생성     } [FORMAT JSON],                          -- 값(val_expr)이 JSON 형식임을 명시     ...   ]   [ JSON_on_null_clause ]                  -- NULL 처리 옵션 (NULL ON NULL 또는 ABSENT ON NULL)   [ JSON_returning_clause ]                -- 반환 데이터 유형 지정 (CLOB, VARCHAR2(size), BLOB 등)   [ STRICT ]                                         -- 값이 유효한 JSON 형식인지 엄격하게 검사   [ WITH UNIQUE KEYS ]                   -- JSON 객체의 키가 중복되지 않도록 강제 )

 

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

 

JSON_OBJECT 기본 사용법

SELECT JSON_OBJECT(
            KEY 'EMPNO' VALUE 7698,
            KEY 'ENAME' VALUE 'BLAKE',
            KEY 'JOB'   VALUE 'MANAGER'
       ) AS json_data
  FROM dual

 

 

JSON_OBJECT 함수는 키 이름(KEY)과 값(VALUE)을 지정하게 쉽게 JSON 객체를 만들 수 있다.

 

SELECT JSON_OBJECT(
            KEY 'EMPNO' VALUE 7698,
            KEY 'ENAME' VALUE 'BLAKE',
            KEY 'JOB'   VALUE 'MANAGER',
            KEY 'DEPT'  VALUE JSON_OBJECT(
                                    KEY 'DEPTNO' VALUE 30,
                                    KEY 'DNAME'  VALUE 'SALES'
                              )
       ) AS json_data
  FROM dual

 

 

JSON_OBJECT 함수는 중첩으로 사용해서 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_OBJECT(
            KEY 'EMPNO' VALUE empno,
            KEY 'ENAME' VALUE ename,
            KEY 'JOB'   VALUE job
       ) AS json_data
  FROM emp

 

 

JSON_OBJECT 함수는 테이블의 여러 행 데이터를 조회하여 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_OBJECT(*) AS json_data
  FROM emp

 

 

JSON_OBJECT 함수에서 별표(*)를 사용하여 모든 칼럼의 데이터를 JSON으로 만들 수 있다.

별표(*)는 오라클 19c 이상의 버전에서 사용할 수 있다.

 

JSON_OBJECT 옵션 사용법

옵션	설명
FORMAT JSON	입력값이 JSON 형식임을 지정
NULL ON NULL	NULL 값을 JSON 객체에 포함
ABSENT ON NULL	NULL 값을 JSON 객체에서 생략
RETURNING	결과 데이터 유형을 VARCHAR2, CLOB, 또는 BLOB로 반환
WITH UNIQUE KEYS	JSON 객체에서 키 중복을 방지 (오라클 21c 이상)

 

FORMAT JSON

SELECT JSON_OBJECT(KEY 'EMP' VALUE '{"ENAME":"BLAKE"}')                    AS res1
     , JSON_OBJECT(KEY 'EMP' VALUE '{"ENAME":"BLAKE"}' FORMAT JSON)        AS res2    
     , JSON_OBJECT(KEY 'EMP' VALUE '{"ENAME":"BLAKE"}' FORMAT JSON STRICT) AS res3    
  FROM dual

 

 

FORMAT JSON 옵션을 사용하면 JSON 형식의 문자열을 합칠 때 문자열을 JSON 형식으로 변환하여 반환한다.
STRICT 옵션을 추가로 사용하면 엄격한 JSON 형식을 체크한 후 반환한다.

 

NULL ON NULL, ABSENT ON NULL

SELECT JSON_OBJECT(
                    KEY 'ENAME' VALUE 'BLAKE', 
                    KEY 'SAL' VALUE NULL
       ) AS result1
     , JSON_OBJECT(
                    KEY 'ENAME' VALUE 'BLAKE', 
                    KEY 'SAL' VALUE NULL NULL ON NULL
       )   AS result2
     , JSON_OBJECT(
                    KEY 'ENAME' VALUE 'BLAKE', 
                    KEY 'SAL' VALUE NULL ABSENT ON NULL
       ) AS result3
  FROM dual

 

 

NULL ON NULL 옵션을 사용하면 NULL 데이터도 포함하여 JSON 배열로 반환한다.
기본 값은 오라클 18c 까지는  NULL을 제외하고, 오라클 19c 이상에서는 NULL을 포함한다.

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

 

RETURNING

SELECT JSON_OBJECT(
            KEY 'EMPNO' VALUE 7698,
            KEY 'ENAME' VALUE 'BLAKE'
            RETURNING VARCHAR2(4000)) AS result1
     , JSON_OBJECT(
            KEY 'EMPNO' VALUE 7698,
            KEY 'ENAME' VALUE 'BLAKE'
            RETURNING CLOB)           AS result2         
     , JSON_OBJECT(
            KEY 'EMPNO' VALUE 7698,
            KEY 'ENAME' VALUE 'BLAKE'
            RETURNING BLOB)           AS result3            
  FROM dual

 

 

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

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