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

오라클 SQL에서 JSON 데이터에서 특정 항목의 JSON 객체( {} ) 또는 배열( [] )을 추출하기 위해서는 JSON_QUERY 함수를 사용하면 된다. JSON_QUERY 함수를 사용할 때는 루트($), 배열 인덱스, 키 이름을 지정하여 원하는 JSON 데이터를 선택할 수 있다. JSON_QUERY 함수는 반환되는 JSON 데이터의 형식은 VARCHAR2(4000)이며, 반환 값의 데이터 유형을  RETURNING 옵션을 사용하여 CLOB, BLOB로 지정할 수 있다. 또한, JSON 데이터를 보기 좋게 포맷하려면 PRETTY 옵션을, 비 ASCII 문자를 이스케이프 하려면 ASCII 옵션을 사용할 수 있다.

 

목차 JSON_QUERY 기본 사용법 JSON_QUERY 옵션 사용법 JSON_QUERY 사용시 주의사항

 

JSON_QUERY 기본 사용법

SELECT JSON_QUERY(
            '{"EMPNO":7698, "ENAME":"BLAKE", DEPT:{"DEPTCD":30, "DNAME":"SALES"}}',
            '$'
       ) AS result
  FROM dual

 

 

JSON_QUERY 함수를 사용하여 JSON 객체 ( {} ), JSON 배열 ( [] )을 추출할 수 있다.

데이터를 추출할 시작점을 루트($)로 설정했으므로 전체 JSON 객체를 반환한다.

 

JSON_QUERY('JSON 데이터', '추출할 시작점')

 

단일 값(문자열, 숫자 등)을 추출하기 위해서는 JSON_VALUE 함수를 사용해야 한다.

 

SELECT JSON_QUERY(
            '{"EMPNO":7698, "ENAME":"BLAKE", DEPT:{"DEPTCD":30, "DNAME":"SALES"}}',
            '$.DEPT'
       ) AS result
  FROM dual

 

 

데이터를 추출할 시작점을 $.DEPT로 지정할 경우 DEPT 키의 JSON 값을 추출한다.

 

배열에서 값을 추출하는 방법

SELECT JSON_QUERY (
            '{
                "DEPTNO": 20,
                "DNAME": "RESEARCH",
                "EMP": [
                    {"EMPNO": 7566, "ENAME": "JONES"},
                    {"EMPNO": 7788, "ENAME": "SCOTT"},
                    {"EMPNO": 7902, "ENAME": "FORD"}
                ]
            }',
            '$.EMP'
       ) AS result
  FROM dual

 

 

EMP 키의 JSON 배열을 추출한다.

 

SELECT JSON_QUERY (
            '{
                "DEPTNO": 20,
                "DNAME": "RESEARCH",
                "EMP": [
                    {"EMPNO": 7566, "ENAME": "JONES"},
                    {"EMPNO": 7788, "ENAME": "SCOTT"},
                    {"EMPNO": 7902, "ENAME": "FORD"}
                ]
            }',
            '$.EMP[0]'
       ) AS result
  FROM dual

 

 

JSON 배열인 경우 배열 인덱스를 지정하여, 해당 인덱스의 객체만 추출할 수 있다.

 

SELECT JSON_QUERY (
            '{
                "DEPTNO":20,
                "DNAME":"RESEARCH",
                "EMP": [
                    {"EMPNO":7566, "ENAME":"JONES"},
                    {"EMPNO":7788, "ENAME":"SCOTT"},
                    {"EMPNO":7902, "ENAME":"FORD"}
                ]
            }',
            '$.EMP[*].ENAME' WITH WRAPPER
       ) AS result
  FROM dual

 

 

WITH WRAPPER 옵션을 사용하여 배열의 특정 값을 배열로 추출할 수 있다.

 

JSON_QUERY 옵션 사용법

옵션	설명
FORMAT JSON	입력 데이터가 JSON 형식임을 명시한다.
RETURNING	반환되는 데이터 유형을 지정한다.  기본값은 VARCHAR2(4000) 이며, CLOB, BLOB를 지정할 수 있다.
PRETTY	반환되는 JSON 문자열을 보기 좋게 들여쓰기와 줄 바꿈을 적용한다.
ASCII	반환되는 JSON 문자열에서 비ASCII 문자를 이스케이프 처리한다.
WITH WRAPPER	반환 결과를 JSON 배열로 감싸서 반환한다.
ON ERROR	JSON 처리 중 오류가 발생할 때의 동작을 지정한다.
ON EMPTY	JSON 경로에 값이 비어 있을 때의 동작을 지정한다.

 

FORMAT JSON (JSON 형식 명시)

SELECT JSON_QUERY(
            '{"EMP":{"EMPNO":7698, "ENAME":"BLAKE"}}' FORMAT JSON,
            '$.EMP' 
       ) AS result
  FROM dual

 

 

FORMAT JSON 옵션을 사용하여 JSON 문자열이 JSON 형식임을 명시하여 값을 추출한다.

 

RETURNING (반환 데이터 유형 지정)

SELECT JSON_QUERY(
            '{"EMP":{"EMPNO":7698, "ENAME":"BLAKE"}}',
            '$.EMP' RETURNING CLOB
       ) AS result
  FROM dual

 

 

RETURNING 옵션을 사용하여 반환 데이터 유형을 지정할 수 있다.

VARHCHAR2(4000), CLOB, BLOB의 데이터 형식을 사용할 수 있다.

 

PRETTY (JSON 문자열을 보기 좋게 변환)

SELECT JSON_QUERY(
            '{"EMP":{"EMPNO":7698, "ENAME":"BLAKE"}}',
            '$.EMP' PRETTY 
       ) AS result
  FROM dual

 

 

PRETTY 옵션을 사용하면 JSON 문자열을 보기 좋게 들여 쓰기와 줄 바꿈을 적용한 후 반환한다.

 

여러 개의 옵션을 사용하는 방법

SELECT JSON_QUERY(
            '{"EMP":{"EMPNO":7698, "ENAME":"BLAKE"}}',
            '$.EMP' RETURNING CLOB PRETTY
       ) AS result
  FROM dual

 

위의 예제는 RETURNING, PRETTY 2개의 옵션을 동시에 사용하는 예제이다.

사용하고 싶은 옵션을 스페이스로 구분하여 여러 개의 옵션을 지정하면 된다.

 

ASCII (ASCII 문자열을 이스케이프 처리)

SELECT JSON_QUERY(
            '{"EMP":{"EMPNO":7698, "ENAME":"BLÃKÉ"}}',
            '$.EMP' 
       ) AS result1
     , JSON_QUERY(
            '{"EMP":{"EMPNO":7698, "ENAME":"BLÃKÉ"}}',
            '$.EMP' ASCII 
       ) AS result2      
  FROM dual

 

 

ASCII 옵션을 사용하면 비 ASCII 문자를 유니코드 이스케이프 형식(\uXXXX)으로 변환되어 반환한다.

 

WITH WRAPPER (반환 결과를 JSON 배열로 감싸서 반환)

SELECT JSON_QUERY(
            '{"EMP":{"EMPNO":7698, "ENAME":"BLAKE"}}',
            '$.EMP' WITH WRAPPER 
       ) AS result
  FROM dual

 

 

WITH WRAPPER 옵션을 사용하면 반환 값을 JSON 배열( [] )로 감싸서 반환한다.

 

ON ERROR (ERROR 발생 시 동작 지정)

SELECT JSON_QUERY(
            '{"EMP":{"EMPNO":7698, "ENAME":"BLAKE"}}',
            '$.EMP.DEPTNO' NULL ON ERROR
       ) AS result
  FROM dual

 

 

NULL ON ERROR 옵션을 사용하면 에러가 발생하면 NULL을 반환한다.

기본 옵션이 적용되므로 선언하지 않아도 에러 발생 시 NULL을 반환한다.

 

SELECT JSON_QUERY(
            '{"EMP":{"EMPNO":7698, "ENAME":"BLAKE"}}',
            '$.EMP.DEPTNO' ERROR ON ERROR
       ) AS result
  FROM dual

 

 

ERROR ON ERROR 옵션을 사용하면 에러가 발생하면 ERROR 코드를 반환한다.

 

ON EMPTY (EMPTY인 경우 동작 지정)

SELECT JSON_QUERY(
            '{"EMP":{"EMPNO":7698, "ENAME":"BLAKE"}}',
            '$.EMP.DEPTNO' NULL ON EMPTY
       ) AS result
  FROM dual

 

 

NULL ON EMPTY 옵션을 사용하면 값이 비어 있으면 NULL을 반환한다.

기본 옵션이 적용되므로 선언하지 않아도 값이 비어 있으면 NULL을 반환한다.

 

SELECT JSON_QUERY(
            '{"EMP":{"EMPNO":7698, "ENAME":"BLAKE"}}',
            '$.EMP.DEPTNO' ERROR ON EMPTY
       ) AS result
  FROM dual

 

 

ERROR ON EMPTY 옵션을 사용하면 값이 비어 있으면 ERROR 코드를 반환한다.

 

JSON_QUERY 사용 시 주의사항

SELECT JSON_QUERY(
            '{"EMPNO":7698, "ENAME":"BLAKE", DEPT:{"DEPTCD":30, "DNAME":"SALES"}}',
            '$.EMPNO'
       ) AS result
  FROM dual

 

 

JSON_QUERY 함수는 단일 값(숫자, 문자열 등)은 추출하지 못한다.

JSON 객체( {} ), 배열( [] )만 추출할 수 있다.

 

SELECT JSON_VALUE(
            '{"EMPNO":7698, "ENAME":"BLAKE", DEPT:{"DEPTCD":30, "DNAME":"SALES"}}',
            '$.EMPNO'
       ) AS result1
     , JSON_QUERY(
            '{"EMPNO":7698, "ENAME":"BLAKE", DEPT:{"DEPTCD":30, "DNAME":"SALES"}}',
            '$.EMPNO' WITH WRAPPER
       ) AS result2
  FROM dual

 

 

JSON 문자열에서 단일 값을 추출하기 위해서는 JSON_VALUE 함수를 사용하거나, JSON_QUERY 함수의 WITH WRAPPER 옵션을 사용하여 배열로 반환해야 한다.