오라클 JSON_QUERY 함수 완벽 정복! JSON 객체와 배열을 자유자재로 추출하는 방법

 

오라클 12c 이후부터 Oracle Database는 JSON 데이터를 다루는 기능을 대폭 강화했습니다. 그중에서도 JSON 객체나 배열을 추출할 때 가장 많이 사용되는 함수가 바로 JSON_QUERY 함수입니다. 이 글에서는 JSON_QUERY 함수의 기본 사용법부터 실무 활용에 필요한 옵션까지, 오라클 전문가 수준의 예제와 함께 깊이 있게 설명합니다. JSON 데이터 처리에 능숙해지고 싶은 오라클 개발자나 DBA 분들에게 강력히 추천하는 필독 가이드입니다.

---

📌 목차

1. JSON_QUERY 함수란?
2. JSON_QUERY 기본 사용법
3. JSON 배열 추출 방법
4. JSON_QUERY 함수 옵션 총정리
5. JSON_QUERY 사용 시 주의사항
6. JSON_VALUE와의 차이점
7. 실무 활용 예시 3가지
8. 마무리 및 팁

---

1. JSON_QUERY 함수란?

JSON_QUERY 함수는 오라클 SQL에서 JSON 객체({}) 또는 배열([])을 추출할 수 있는 함수입니다. 단일 값(숫자, 문자열 등)은 JSON_VALUE를 사용해야 하며, JSON_QUERY는 JSON 구조 자체를 반환하는 데 특화되어 있습니다.

기본 형식:

JSON_QUERY(json_string, json_path [options])

• json_string: JSON 형식의 문자열
• json_path: 추출할 경로, 보통 $.key 또는 $.key[index] 형식
• options: RETURNING, PRETTY, ASCII, WITH WRAPPER 등 다양한 옵션 설정 가능

기본적으로 반환 형식은 VARCHAR2(4000)이며, RETURNING CLOB을 지정하면 더 큰 데이터를 처리할 수 있습니다.

---

2. JSON_QUERY 기본 사용법

다음은 가장 기본적인 JSON 객체 전체를 추출하는 예제입니다.

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

📌 '$' 경로는 루트를 의미하며, JSON 전체를 반환합니다.

특정 키의 JSON 객체를 추출할 수도 있습니다.

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

이 경우 DEPT 키에 해당하는 JSON 객체만 반환됩니다.

---

3. JSON 배열 추출 방법

배열 추출은 실무에서 자주 사용되는 패턴입니다. 다음은 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는 EMP 키에 해당하는 배열 전체를 반환합니다.

특정 인덱스의 배열 요소만 추출하려면 다음과 같이 작성합니다.

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

👆 인덱스는 0부터 시작합니다. 위 예제는 "SCOTT" 데이터를 반환합니다.

배열 내부 특정 필드를 추출하고자 할 경우, WITH WRAPPER 옵션을 사용하면 좋습니다.

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

🔄 WITH WRAPPER는 여러 개의 값을 배열 형식으로 감싸서 반환합니다.

---

4. JSON_QUERY 함수 옵션 총정리

옵션명 설명

FORMAT JSON	입력 문자열이 JSON임을 명시
RETURNING	반환 데이터 타입 지정 (VARCHAR2, CLOB, BLOB)
PRETTY	보기 좋게 들여쓰기 적용
ASCII	비 ASCII 문자를 유니코드 이스케이프 처리
WITH WRAPPER	결과를 배열로 감싸서 반환
ON ERROR	오류 발생 시 동작 지정 (NULL 또는 ERROR)
ON EMPTY	값이 없을 때의 동작 지정

✅ 예시: 여러 옵션 조합

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

RETURNING CLOB과 PRETTY를 동시에 사용한 예입니다. 실무에서는 대용량 JSON 데이터를 보기 좋게 출력할 때 유용합니다.

---

5. JSON_QUERY 사용 시 주의사항

JSON_QUERY는 단일 값을 추출할 수 없습니다. 예를 들어, 숫자나 문자열 하나만 추출하려 하면 NULL이 반환되거나 에러가 발생합니다.

-- 잘못된 사용 (단일 값 추출 시)
SELECT JSON_QUERY(
  '{"EMPNO":7698, "ENAME":"BLAKE"}',
  '$.EMPNO'
) AS result
FROM dual;

이럴 경우에는 반드시 JSON_VALUE 함수를 사용해야 합니다.

---

6. JSON_VALUE와의 차이점

함수명 추출 가능 대상 반환 형식

JSON_QUERY	객체 {} 또는 배열 []	JSON 문자열 (VARCHAR2, CLOB)
JSON_VALUE	숫자, 문자열, 불린 등 단일 값	기본 SQL 타입 (NUMBER, VARCHAR 등)

SELECT JSON_VALUE(
  '{"EMPNO":7698, "ENAME":"BLAKE"}',
  '$.EMPNO'
) AS empno
FROM dual;

JSON_VALUE는 단일 값 추출에 적합한 함수입니다.

---

7. 실무 활용 예시 3가지

예제 1. 직원 리스트를 JSON 배열로 출력

SELECT JSON_QUERY(
  '{"DEPTNO":10, "EMP":[{"ENAME":"SMITH"}, {"ENAME":"ALLEN"}]}',
  '$.EMP' WITH WRAPPER
) AS emp_list
FROM dual;

예제 2. JSON 보기 좋게 출력하기 (PRETTY + RETURNING)

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

예제 3. 에러 발생 시 NULL 처리

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

---

8. 마무리 및 팁

오라클에서 JSON 데이터를 다루는 기능은 계속해서 발전하고 있으며, JSON_QUERY는 그 중심에 있는 핵심 함수입니다. 기본 문법에 익숙해지는 것뿐만 아니라 옵션의 조합, 배열과 객체 구분, JSON_VALUE와의 병행 사용 등을 익히면 훨씬 더 유연하고 강력한 JSON 처리 SQL을 작성할 수 있습니다.

🔑 팁 요약

• JSON 객체, 배열 추출: JSON_QUERY
• 단일 값 추출: JSON_VALUE
• 결과 보기 좋게: PRETTY
• 결과 길게 출력: RETURNING CLOB
• 배열 내부 요소만: [*].key + WITH WRAPPER