오라클 JSON_TABLE 함수 완벽 가이드 – JSON 데이터를 테이블처럼 활용하기

 

오라클 데이터베이스는 전통적인 관계형 데이터뿐만 아니라 JSON 데이터도 유연하게 처리할 수 있도록 다양한 기능을 제공하고 있습니다. 그중에서도 JSON_TABLE 함수는 JSON 데이터를 테이블 형식으로 변환해 SQL로 쉽게 조회하고 조인할 수 있도록 해주는 매우 유용한 도구입니다.

이번 포스팅에서는 오라클 12c 이상 버전에서 사용할 수 있는 JSON_TABLE 함수의 기본 문법부터 실무에 적용 가능한 활용 예제까지, 단계별로 상세하게 정리해보았습니다.

---

📌 목차

1. JSON_TABLE 함수란?
2. JSON_TABLE 기본 문법 및 사용 예제
3. JSON 배열과 중첩 JSON 처리하기
4. 테이블 내 JSON 칼럼 변환 및 조인 방법
5. JSON_TABLE 함수의 고급 옵션 활용법
6. 실무 적용 시 유의사항 및 팁

---

1. JSON_TABLE 함수란?

JSON_TABLE 함수는 JSON 데이터를 관계형 테이블 형식으로 펼쳐주는 SQL 함수입니다. 특히 JSON 내부의 필드를 칼럼처럼 매핑하여 SELECT, JOIN, WHERE 절 등에서 자유롭게 사용할 수 있게 해줍니다.

이 함수는 다음과 같은 상황에서 유용하게 활용됩니다.

• JSON 컬럼이 있는 테이블을 정규화하여 조회하고 싶은 경우
• JSON 응답 데이터를 SQL로 직접 분석해야 하는 경우
• 중첩 JSON 구조를 정형화하여 활용하고 싶은 경우

사용 가능 버전: Oracle 12c 이상

---

2. JSON_TABLE 기본 문법 및 사용 예제

SELECT jt.empno, jt.ename
  FROM JSON_TABLE (
        '{"EMPNO":7698,"ENAME":"BLAKE"}', 
        '$'
        COLUMNS (
            empno NUMBER PATH '$.EMPNO',
            ename VARCHAR2(10) PATH '$.ENAME'
        )
    ) jt;

🔍 문법 설명

• JSON_TABLE (json_data, '$' COLUMNS (...)): JSON 데이터를 시작 경로 $부터 파싱합니다.
• COLUMNS: JSON 키를 칼럼으로 변환할 때 칼럼명, 데이터 타입, JSON 경로(PATH)를 지정합니다.
• 데이터 타입: VARCHAR2, NUMBER, DATE, CLOB 등 사용 가능

---

3. JSON 배열과 중첩 JSON 처리하기

✅ JSON 객체 배열

SELECT jt.empno, jt.ename
  FROM JSON_TABLE (
        '[{"EMPNO":7698,"ENAME":"BLAKE"},{"EMPNO":7782,"ENAME":"CLARK"}]',
        '$[*]'
        COLUMNS (
            empno NUMBER PATH '$.EMPNO',
            ename VARCHAR2(10) PATH '$.ENAME'
        )
    ) jt;

• '$[*]': 배열 전체를 순회하며 각 항목을 행으로 변환합니다.

✅ 배열 내 특정 인덱스만 추출

SELECT jt.empno, jt.ename
  FROM JSON_TABLE (
        '[{"EMPNO":7698,"ENAME":"BLAKE"},{"EMPNO":7782,"ENAME":"CLARK"}]',
        '$[1]'
        COLUMNS (
            empno NUMBER PATH '$.EMPNO',
            ename VARCHAR2(10) PATH '$.ENAME'
        )
    ) jt;

• $[1]: 배열의 2번째 요소만 가져옵니다. (인덱스는 0부터 시작)

✅ 중첩 JSON 구조 처리

SELECT jt.empno, jt.ename
  FROM JSON_TABLE (
        '{"EMP":[{"EMPNO":7698,"ENAME":"BLAKE"},{"EMPNO":7782,"ENAME":"CLARK"}]}',
        '$.EMP[*]'
        COLUMNS (
            empno NUMBER PATH '$.EMPNO',
            ename VARCHAR2(10) PATH '$.ENAME'
        )
    ) jt;

• 중첩된 키 EMP 하위의 배열을 조회할 수 있도록 경로를 $.EMP[*]로 지정합니다.

---

4. 테이블 내 JSON 칼럼 변환 및 조인

✅ JSON 컬럼을 테이블로 변환

WITH emp_json AS (
  SELECT 7698 empno, '{"EMPNO":7698,"ENAME":"BLAKE","DEPTNO":30}' emp_data FROM dual UNION ALL
  SELECT 7782 empno, '{"EMPNO":7782,"ENAME":"CLARK","DEPTNO":20}' emp_data FROM dual
)
SELECT e.empno, jt.ename
  FROM emp_json e,
       JSON_TABLE (
            e.emp_data, '$'
            COLUMNS (
                empno NUMBER PATH '$.EMPNO',
                ename VARCHAR2(10) PATH '$.ENAME'
            )
       ) jt;

✅ JOIN을 활용한 조인 예제

SELECT e.empno, jt.ename
  FROM emp_json e
  JOIN JSON_TABLE (
        e.emp_data, '$'
        COLUMNS (
            empno NUMBER PATH '$.EMPNO',
            ename VARCHAR2(10) PATH '$.ENAME'
        )
      ) jt
    ON e.empno = jt.empno;

• 조인 조건을 활용하면 크로스 조인이 아닌 정확한 매칭을 통한 조회가 가능합니다.

✅ 다른 테이블과의 조인

SELECT e.empno, jt.ename, jt.deptno, d.dname
  FROM emp_json e
  JOIN JSON_TABLE (
        e.emp_data, '$'
        COLUMNS (
            empno NUMBER PATH '$.EMPNO',
            ename VARCHAR2(10) PATH '$.ENAME',
            deptno NUMBER PATH '$.DEPTNO'
        )
      ) jt
    ON e.empno = jt.empno
  JOIN dept d
    ON d.deptno = jt.deptno;

---

5. JSON_TABLE 함수의 고급 옵션 활용법

🔹 FOR ORDINALITY

SELECT jt.idx, jt.empno, jt.ename
  FROM JSON_TABLE (
        '[{"EMPNO":7698,"ENAME":"BLAKE"},{"EMPNO":7782,"ENAME":"CLARK"}]', 
        '$[*]'
        COLUMNS (
            idx FOR ORDINALITY,
            empno NUMBER PATH '$.EMPNO',
            ename VARCHAR2(10) PATH '$.ENAME'
        )
    ) jt;

• 배열의 순번 인덱스를 반환합니다.

🔹 NESTED PATH

SELECT jt.deptno, jt.dname, jt.empno, jt.ename
  FROM JSON_TABLE (
        '{ "DEPT": [
            {"DEPTNO": 10, "DNAME": "ACCOUNTING", "EMP": [
                {"EMPNO": 7839, "ENAME": "KING"},
                {"EMPNO": 7782, "ENAME": "CLARK"}
            ]},
            {"DEPTNO": 20, "DNAME": "RESEARCH", "EMP": [
                {"EMPNO": 7566, "ENAME": "JONES"},
                {"EMPNO": 7788, "ENAME": "SCOTT"},
                {"EMPNO": 7902, "ENAME": "FORD"}
            ]}
        ] }',
        '$.DEPT[*]'
        COLUMNS (
            deptno NUMBER PATH '$.DEPTNO',
            dname  VARCHAR2(50) PATH '$.DNAME',
            NESTED PATH '$.EMP[*]' COLUMNS (
                empno NUMBER PATH '$.EMPNO',
                ename VARCHAR2(50) PATH '$.ENAME'
            )
        )
    ) jt;

• 중첩 배열을 계층 구조로 펼칠 수 있습니다.

🔹 DEFAULT ON EMPTY / ON ERROR

SELECT jt.empno, jt.ename, jt.sal
  FROM JSON_TABLE (
        '[{"EMPNO":7698,"ENAME":"BLAKE"},{"EMPNO":7782,"ENAME":"CLARK"}]',
        '$[*]'
        COLUMNS (
            empno NUMBER PATH '$.EMPNO',
            ename VARCHAR2(10) PATH '$.ENAME',
            sal   NUMBER PATH '$.SAL' DEFAULT 0 ON EMPTY
        )
    ) jt;

• ON EMPTY는 값이 비었을 때 기본값을,
• ON ERROR는 오류 발생 시 기본값을 설정합니다.

---

6. 실무 적용 시 유의사항 및 팁

• JSON 데이터 형식이 올바르지 않으면 오류가 발생하므로 사전 검증이 필요합니다.
• 성능에 민감한 환경에서는 JSON_TABLE 사용 시 인덱스 전략을 함께 고려해야 합니다.
• JSON_TABLE은 일반 SQL 테이블처럼 JOIN, 필터링, 서브쿼리 등과 자연스럽게 통합됩니다.

---

✅ 마무리

오라클에서 JSON 데이터를 다루는 일이 점점 많아지는 지금, JSON_TABLE 함수는 JSON을 SQL 수준에서 유연하게 처리할 수 있게 해주는 강력한 기능입니다. 특히 중첩 구조나 배열 데이터를 손쉽게 분해하고 다른 테이블과 조인할 수 있어 실무에서 매우 유용하게 활용됩니다.

이 포스팅을 통해 JSON_TABLE의 기초 문법부터 고급 기능까지 확실히 숙지하시고, 다양한 실무 시나리오에 적극적으로 활용해보시기 바랍니다.