[Oracle] 오라클 JSON_VALUE 함수 사용법
- 데이터베이스/오라클
- 2025. 1. 5.
오라클 SQL에서 JSON 데이터에서 특정 항목의 값을 추출하기 위해서 JSON_VALUE 함수를 사용하면 된다. JSON_VALUE 함수를 사용하여 값을 추출할 때는 루트($)와 배열순번, 키 이름을 지정하면 된다. JSON_VALUE 함수를 사용할 때 반환 값의 데이터 유형을 지정하기 위해서는 RETURNING 옵션을 사용하면 되며, 데이터 추출 중 오류가 발행하거나 값이 비어있을 경우 ON ERROR, ON EMPTY 옵션을 사용하여 처리할 수 있다.
JSON_VALUE 함수는 오라클 21c 부터 사용할 수 있다.
| 목차 |
JSON_VALUE 기본 사용법
SELECT JSON_VALUE('{"EMPNO":7698,"ENAME":"BLAKE"}', '$.EMPNO') AS json_val1
, JSON_VALUE('{"EMPNO":7698,"ENAME":"BLAKE"}', '$.ENAME') AS json_val2
FROM dual
 |
JSON_VALUE 함수를 사용하여 JSON 객체 ( {} )에서 $.[키 이름] 으로 값을 가져올 수 있다.
$는 JSON 객체의 루트(root)를 의미한다.
중첩 JSON 객체에서 값 가져오기
SELECT JSON_VALUE('{"EMP":{"EMPNO":7698,"ENAME":"BLAKE"}}','$.EMP.EMPNO') AS json_val1
, JSON_VALUE('{"EMP":{"EMPNO":7698,"ENAME":"BLAKE"}}','$.EMP.ENAME') AS json_val2
FROM dual
 |
중첩 JSON 객체인 경우 $.[키 이름].[키 이름] 으로 값을 가져올 수 있다.
배열에서 값 가져오기
SELECT JSON_VALUE('["BLAKE","CLARK","JONES"]', '$[0]') AS json_val1
, JSON_VALUE('["BLAKE","CLARK","JONES"]', '$[1]') AS json_val2
, JSON_VALUE('["BLAKE","CLARK","JONES"]', '$[2]') AS json_val3
FROM dual
 |
JSON_VALUE 함수를 사용하여 배열( [] )에서 $[배열순번] 으로 값을 가져올 수 있다.
JSON 배열에서 값 가져오기
SELECT JSON_VALUE('[{"ENAME":"BLAKE"},{"ENAME":"CLARK"}]', '$[0].ENAME') AS json_val1
, JSON_VALUE('[{"ENAME":"BLAKE"},{"ENAME":"CLARK"}]', '$[1].ENAME') AS json_val2
FROM dual
 |
배열에 JSON 객체가 나열되어 있을 경우 $[배열순번].[키 이름] 으로 값을 가져올 수 있다.
배열에 JSON 객체가 1개만 존재할 경우 배열순번을 제외하고 $.[키 이름] 으로 사용해도 된다.
테이블 JSON 칼럼에서 값 가져오기
WITH emp_json AS (
SELECT CAST('{"EMPNO":7698,"ENAME":"BLAKE","SAL":2850}' AS VARCHAR2(4000)) json_data FROM dual UNION ALL
SELECT CAST('{"EMPNO":7782,"ENAME":"CLARK","SAL":2450}' AS VARCHAR2(4000)) json_data FROM dual UNION ALL
SELECT CAST('{"EMPNO":7566,"ENAME":"JONES","SAL":2975}' AS VARCHAR2(4000)) json_data FROM dual
)
SELECT JSON_VALUE(json_data, '$.EMPNO') AS json_val1
, JSON_VALUE(json_data, '$.ENAME') AS json_val2
, JSON_VALUE(json_data, '$.SAL') AS json_val3
FROM emp_json
 |
JSON_VALUE 함수를 사용하여 테이블의 JSON 칼럼에서 값을 가져올 수 있다.
JSON_VALUE 옵션 사용법
| 옵션 | 설명 |
| RETURNING | 반환되는 데이터 유형을 지정한다. 기본값은 VARCHAR2(4000) 이며, NUMBER, DATE, CLOB 등을 지정할 수 있다. |
| DEFAULT | 값이 비어 있거나 오류가 발생할 때 반환할 기본값을 지정한다. |
| ON ERROR | JSON 처리 중 오류가 발생할 때의 동작을 지정한다. |
| ON EMPTY | JSON 경로에 값이 비어 있을 때의 동작을 지정한다. |
반환 데이터 유형 지정 (RETURNING)
WITH emp_json AS (
SELECT CAST('{"EMPNO":7698,"ENAME":"BLAKE","HIREDATE":"1981-05-01"}' AS VARCHAR2(4000)) json_data FROM dual UNION ALL
SELECT CAST('{"EMPNO":7782,"ENAME":"CLARK","HIREDATE":"1981-06-09"}' AS VARCHAR2(4000)) json_data FROM dual UNION ALL
SELECT CAST('{"EMPNO":7566,"ENAME":"JONES","HIREDATE":"1981-04-02"}' AS VARCHAR2(4000)) json_data FROM dual
)
SELECT JSON_VALUE(json_data, '$.EMPNO' RETURNING NUMBER) AS json_val1
, JSON_VALUE(json_data, '$.ENAME' RETURNING VARCHAR2(10)) AS json_val2
, JSON_VALUE(json_data, '$.HIREDATE' RETURNING DATE) AS json_val3
FROM emp_json
 |
JSON_VALUE 함수의 끝에 VARCHAR2, NUMBER, DATE, CLOB 등의 반환받을 데이터 유형을 지정할 수 있다.
오류 또는 값이 비어 있을 경우 기본 값 지정 (DEFAULT)
SELECT JSON_VALUE('{"ENAME":"SCOTT"}', '$.SAL' DEFAULT '0' ON ERROR) AS result1
, JSON_VALUE('{"ENAME":"SCOTT"}', '$.SAL' DEFAULT '0' ON EMPTY) AS result2
FROM dual
 |
오류가 발생했거나 지정한 키 이름($.SAL)이 비어 있을 경우 기본 값(DEFAULT)을 지정할 수 있다.
위의 예제는 SAL이라는 키가 존재하기 않기 때문에 ON ERROR와 ON EMPTY가 동시에 발생한다.
오류 발생 시 처리 방법 (ON ERROR)
SELECT JSON_VALUE('{"ENAME":"SCOTT"}', '$.SAL' NULL ON ERROR) AS null_on_error_result
FROM dual
 |
NULL ON ERROR 옵션은 오류가 발생하면 NULL을 반환한다.
SELECT JSON_VALUE('{"ENAME":"SCOTT"}', '$.SAL' ERROR ON ERROR) AS error_on_error_result
FROM dual
 |
ERROR ON ERROR 옵션은 오류가 발생하면 에러 코드를 반환한다.
값이 비어 있을 경우 처리 방법 (ON EMPTY)
SELECT JSON_VALUE('{"ENAME":"SCOTT"}', '$.SAL' NULL ON EMPTY) AS null_on_empty_result
FROM dual
 |
NULL ON EMPTY 옵션은 값이 비어 있으면 NULL을 반환한다.
SELECT JSON_VALUE('{"ENAME":"SCOTT"}', '$.SAL' ERROR ON EMPTY) AS error_on_empty_result
FROM dual
 |
ERROR ON EMPTY 옵션은 값이 비어 있으면 에러 코드를 반환한다.
여러 개의 옵션을 적용하는 방법
SELECT JSON_VALUE('{"ENAME":"SCOTT"}', '$.SAL'
RETURN NUMBERR NULL ON ERROR) AS null_on_error_result
FROM dual
JSON_VALUE 함수의 끝에 사용할 옵션을 스페이스로 구분하여 입력하면 된다.