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

오라클 SQL에서 JSON 데이터를 배열로 반환하기 위해서는 JSON_ARRAY 함수를 사용하면 된다. JSON_ARRAY 함수는 오라클 12c R2 이상에서 사용할 수 있다. JSON_ARRAY 함수는 입력된 데이터를 단순 배열로 반환하는 함수라고 생각하면 되며, 각 데이터별로 옵션을 사용하여 NULL 또는 JSON 형식을 체크할 수 있다.

 

JSON_ARRAY(   expr [FORMAT JSON],       -- JSON 배열에 포함할 값 (JSON 형식인지 지정 가능)   expr [FORMAT JSON],       -- 추가 값 (콤마로 구분)   ...   [JSON_on_null_clause]     -- NULL 처리 옵션 (NULL ON NULL | ABSENT ON NULL)   [JSON_returning_clause]   -- 반환 데이터 유형 (RETURNING CLOB | VARCHAR2(size) | BLOB)   [STRICT]                            -- JSON 유효성 검사 (엄격 모드) )

 

 

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

 

JSON_ARRAY 기본 사용법

SELECT JSON_ARRAY(7566, 'JONES', 'MANAGER', 2975) AS json_data
  FROM dual

 

 

JSON_ARRAY 함수에 입력된 데이터를 JSON 배열로 반환한다.

 

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

SELECT JSON_ARRAY(json_data FORMAT JSON)
  FROM emp_json

 

 

테이블 칼럼의 값을 JSON 배열로 반환할 수 있다.

칼럼의 값이 일반적인 값이거나, JSON 문자열이거나 상관없이 입력된 값을 배열로 반환한다.

 

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

SELECT JSON_ARRAYAGG(json_data FORMAT JSON)
  FROM emp_json

 

 

테이블의 여러 행의 JSON 문자열 칼럼을 하나의 배열로 반환하기 위해서는,

JSON_ARRAYAGG 함수를 사용하면 된다.

 

JSON_ARRAY 옵션 사용법

옵션	설명
FORMAT JSON	입력값이 JSON 형식임을 지정
NULL ON NULL	NULL 값을 JSON 배열에 포함
ABSENT ON NULL	NULL 값을 생략
RETURNING	결과 데이터 유형을 VARCHAR2, CLOB, 또는 BLOB로 반환

 

FORMAT JSON

SELECT JSON_ARRAY(7698, '{"ENAME":"BLAKE"}')             AS result1
     , JSON_ARRAY(7698, '{"ENAME":"BLAKE"}' FORMAT JSON) AS result2
  FROM dual

 

 

JSON  형식의 값(칼럼)에 FORMAT JSON 옵셥을 부여하면 JSON 데이터로 반환한다.

JSON 형식이 아닌 값에 FORMAT JSON 옵션을 부여하면 오류가 발행하니 주의해야 한다.

 

SELECT JSON_ARRAY('{"EMPNO":7698}' FORMAT JSON, '{"ENAME":"BLAKE"}' FORMAT JSON)
     , JSON_ARRAY('{"EMPNO":7698}', '{"ENAME":"BLAKE"}' FORMAT JSON STRICT) 
  FROM dual

 

 

FORMAT JSON 옵션은 각각 값(칼럼)에 부여할 수 있으며,

STRICT 옵션을 추가로 부여하면 엄격한 JSON 형식을 체크한다.

 

NULL ON NULL, ABSENT ON NULL

SELECT JSON_ARRAY(7566, 'JONES', NULL, 2850)                AS result1
     , JSON_ARRAY(7566, 'JONES', NULL, 2450 NULL ON NULL)   AS result2
     , JSON_ARRAY(7566, 'JONES', NULL, 2975 ABSENT ON NULL) AS result3
  FROM dual

 

 

NULL OR NULL 옵션을 부여하면 값이 NULL인 경우도 NULL로 반환하며,

옵션을 부여하지 않거나(기본값), ABSENT ON NULL 옵션을 부여하면 NULL인 경우 값을 생략한다.

 

NULL ON NULL 옵션을 입력값의 맨 끝에만 사용할 수 있다.

 

RETURNING

SELECT JSON_ARRAY(7566, 'JONES', NULL, 2850)                          AS result1
     , JSON_ARRAY(7566, 'JONES', NULL, 2450 RETURNING VARCHAR2(4000)) AS result2
     , JSON_ARRAY(7566, 'JONES', NULL, 2975 RETURNING CLOB)           AS result3
     , JSON_ARRAY(7566, 'JONES', NULL, 2975 RETURNING BLOB)           AS result4    
  FROM dual

 

 

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

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

 

여러 옵션을 동시에 부여하는 법

 

여러 옵션을 동시에 부여할 때는 공백으로 구분하여 옵션을 입력하면 된다.