[Oracle] JSON_VALUE 함수 사용법 완벽 가이드 – 실무 예제로 배우는 오라클 JSON 처리

 

오라클 12c부터 JSON 데이터를 처리할 수 있도록 다양한 JSON 관련 함수들이 제공되기 시작했습니다. 그중에서도 JSON_VALUE 함수는 JSON 데이터에서 특정 항목의 값을 간단하게 추출할 수 있는 핵심 함수입니다. 특히 JSON 형태의 문자열을 저장하는 칼럼이 있는 경우, 이 함수를 통해 원하는 정보를 SELECT 문 안에서 바로 추출할 수 있어 매우 유용합니다.

이 글에서는 JSON_VALUE 함수의 기본 문법, 중첩 객체 및 배열 처리 방법, RETURNING, DEFAULT, ON ERROR, ON EMPTY 옵션 사용법까지 실무 중심으로 상세히 설명드리겠습니다.

---

✅ JSON_VALUE 함수란?

• 오라클 12c부터 제공
• JSON 문자열에서 특정 키의 값을 추출할 때 사용
• 구문 형태:
• JSON_VALUE(json_data, '경로표현식' RETURNING 데이터유형 DEFAULT 기본값 ON ERROR|ON EMPTY)
• 사용 가능한 옵션:
  • RETURNING: 반환 데이터의 유형을 명시
  • DEFAULT: 값이 없거나 오류 시 기본값 지정
  • ON ERROR: 오류 발생 시 동작 제어
  • ON EMPTY: 경로에 값이 없을 때 동작 제어

---

📌 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의 루트(root)
• $.키이름 형식으로 값을 추출
• 결과:
• json_val1: 7698 json_val2: BLAKE

---

📌 중첩 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;

• 중첩 객체는 $.외부키.내부키 구조로 접근

---

📌 배열에서 값 추출하기

✅ 단순 문자열 배열

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 객체가 있을 경우

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 값 추출

WITH emp_json AS (
    SELECT '{"EMPNO":7698,"ENAME":"BLAKE","SAL":2850}' AS json_data FROM dual UNION ALL
    SELECT '{"EMPNO":7782,"ENAME":"CLARK","SAL":2450}' FROM dual UNION ALL
    SELECT '{"EMPNO":7566,"ENAME":"JONES","SAL":2975}' FROM dual
)
SELECT JSON_VALUE(json_data, '$.EMPNO') AS empno,
       JSON_VALUE(json_data, '$.ENAME') AS ename,
       JSON_VALUE(json_data, '$.SAL')   AS sal
  FROM emp_json;

• 실무에서 흔히 사용되는 패턴
• VARCHAR2로 저장된 JSON 칼럼에서 원하는 값을 추출 가능

---

📌 RETURNING 옵션 사용 – 반환 유형 지정

SELECT JSON_VALUE(json_data, '$.EMPNO' RETURNING NUMBER)       AS empno,
       JSON_VALUE(json_data, '$.ENAME' RETURNING VARCHAR2(10)) AS ename,
       JSON_VALUE(json_data, '$.HIREDATE' RETURNING DATE)      AS hiredate
  FROM emp_json;

• 기본 반환 유형은 VARCHAR2(4000)
• 숫자, 날짜, CLOB 등으로 반환 형식을 명시하면 성능과 타입 변환 측면에서 유리

---

📌 DEFAULT, ON ERROR, ON EMPTY 옵션 활용

SELECT JSON_VALUE('{"ENAME":"SCOTT"}', '$.SAL' DEFAULT '0' ON ERROR) AS val_on_error,
       JSON_VALUE('{"ENAME":"SCOTT"}', '$.SAL' DEFAULT '0' ON EMPTY) AS val_on_empty
  FROM dual;

• 존재하지 않는 키 접근 시, 기본값 0으로 반환
• ON ERROR는 잘못된 경로, JSON 파싱 에러 발생 시 동작
• ON EMPTY는 값은 없지만 JSON 경로는 올바른 경우에 해당

---

📌 오류 처리 상세 옵션 예시

✅ NULL 반환

SELECT JSON_VALUE('{"ENAME":"SCOTT"}', '$.SAL' NULL ON ERROR) FROM dual;
SELECT JSON_VALUE('{"ENAME":"SCOTT"}', '$.SAL' NULL ON EMPTY) FROM dual;

✅ 오류 강제 발생

SELECT JSON_VALUE('{"ENAME":"SCOTT"}', '$.SAL' ERROR ON ERROR) FROM dual;
SELECT JSON_VALUE('{"ENAME":"SCOTT"}', '$.SAL' ERROR ON EMPTY) FROM dual;

• 프로세스나 예외 로직 제어 시 유용

---

📌 여러 옵션을 동시에 사용하는 방법

SELECT JSON_VALUE('{"ENAME":"SCOTT"}', '$.SAL'
        RETURNING NUMBER DEFAULT 0 ON ERROR NULL ON EMPTY) AS result
  FROM dual;

• 옵션은 스페이스로 구분하여 여러 개 적용 가능

---

✅ 마무리

JSON_VALUE 함수는 JSON 데이터를 효율적으로 처리할 수 있는 매우 유용한 도구입니다. 단순한 문자열 추출에서부터, 중첩된 구조나 배열 처리, 오류 대응 로직까지 폭넓게 활용할 수 있습니다. 특히, 실무에서는 JSON 칼럼을 가진 테이블에서 특정 값만 빠르게 조회할 때 유용하게 사용됩니다.

💡 실무 팁:

• JSON 데이터가 빈번하게 조회되는 경우에는 RETURNING 옵션으로 형변환을 명시해두면 쿼리 성능 향상에 도움이 됩니다.
• 항상 ON ERROR, ON EMPTY 옵션을 함께 고려하여 예외 상황을 방지하세요.