젠트의 프로그래밍 세상

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

오라클 SQL에서 JSON 데이터를 테이블로 변환할 때는 JSON_TABLE 함수를 사용하면 된다. JSON_TABLE 함수를 사용할 때는 JSON 데이터를 직접 입력하여 사용해도 되고, 테이블에 포함된 JSON 칼럼의 데이터를 테이블로 변환할 수도 있다. JSON_TABLE 함수는 오라클 12c 이상에서 사용할 수 있다.

 

목차

 

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  데이터], [시작점 경로], [JSON 칼럼] )

COLUMNS 부분은 [칼럼명] [데이터 유형] PATH [JSON 칼럼명]으로 입력하면 된다.

데이터 유형은 VARCHAR2, NUMBER, DATE, CLOB 등을 사용할 수 있다.

 

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

 

 

JSON 객체 배열인 경우 시작점 경로를 $[*]로 지정할 수 있다.( *: 전체 배열을 의미함)

 

JSON 객체 배열의 특정 순번의 데이터만 사용할 경우

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

 

 

JSON 배열의 특정 순번의 데이터만 사용할 경우,  시작점 경로를 $[순번]으로 지정하면 된다.

 

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

 

 

중첩 JSON 구조인 경우 $.[키 이름][배열]로 시작점 경로를 지정하면 된다.

 

{"EMP":{"EMPNO":7698,"ENAME":"BLAKE"}}

위의 JSON 구조처럼 중첩 JSON 구조지만 하위 객체가 배열이 아닌 경우, 시작점 경로를 $.EMP로 지정하면 된다.

 

JSON_TABLE 테이블 JSON 사용 및 조인 방법

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

 

 

위의 예제는 emp_json 테이블에 포함된 emp_data JSON 데이터 칼럼을 테이블로 변환하는 예제이다.

테이블에 포함된 JSON 데이터 칼럼을 JSON_TABLE 함수를 사용하여 해당 칼럼을 테이블로 만들 수 있다.

 

FROM 절에 나열된 emp_json테이블과 JSON_TABLE()에 조인 조건을 지정하지 않았으므로 크로스 조인이 된다.

 

JSON_TABLE에 조인 조건을 지정하는 방법

WITH emp_json AS (
  SELECT 7698 empno, CAST('{"EMPNO":7698,"ENAME":"BLAKE","DEPTNO":30}' AS VARCHAR2(4000)) emp_data FROM dual UNION ALL
  SELECT 7782 empno, CAST('{"EMPNO":7782,"ENAME":"CLARK","DEPTNO":20}' AS VARCHAR2(4000)) emp_data FROM dual UNION ALL
  SELECT 7566 empno, CAST('{"EMPNO":7566,"ENAME":"JONES","DEPTNO":20}' AS VARCHAR2(4000)) emp_data FROM dual
)  
       
SELECT e.empno
     , e.emp_data
     , 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

 

 

테이블과 JSON_TABLE에 조인이 가능한 키 칼럼이 있는 경우 조인을 할 수 있다.

INNER JOIN(JOIN), LEFT OUTER JOIN 등의 조인을 사용할 수 있다.

 

JSON_TABLE에 여러 테이블을 조인하는 방법

WITH emp_json AS (
  SELECT 7698 empno, CAST('{"EMPNO":7698,"ENAME":"BLAKE","DEPTNO":30}' AS VARCHAR2(4000)) emp_data FROM dual UNION ALL
  SELECT 7782 empno, CAST('{"EMPNO":7782,"ENAME":"CLARK","DEPTNO":20}' AS VARCHAR2(4000)) emp_data FROM dual UNION ALL
  SELECT 7566 empno, CAST('{"EMPNO":7566,"ENAME":"JONES","DEPTNO":20}' AS VARCHAR2(4000)) emp_data FROM dual
), dept AS (
  SELECT 10 deptno, 'ACCOUNTING' dname FROM dual UNION ALL
  SELECT 20 deptno, 'RESEARCH'   dname FROM dual UNION ALL
  SELECT 30 deptno, 'SALES'      dname FROM dual UNION ALL
  SELECT 40 deptno, 'OPERATIONS' dname FROM dual
)    
       
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

 

 

emp_json 테이블, JSON_TABLE, dept 테이블 3개의 테이블을 조인하는 예제이다.

 

JSON_TABLE 옵션 사용법

옵션  설명
 FOR ORDINALITY  JSON 배열의 순번(인덱스)를 반환한다.
 NESTED PATH  중첩된 JSON 배열을 처리하여 개별 행으로 분리한다.
 DEFAULT  값이 없거나 오류가 발생할 때 반환할 기본값을 지정한다.
 ON EMPTY  JSON 경로에 값이 비어 있을 때의 동작을 지정한다.
 ON ERROR  JSON 처리 중 오류가 발생할 때의 동작을 지정한다.

 

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

 

 

FOR ORDINALITY 옵션을 사용하면 JSON 배열의 순번을 표시할 수 있다.

 

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

 

 

NESTED PATH 옵션을 사용하면 중첩된 하위 배열의 개수만큼 여러 행으로 분리한다.

위의 예제에서 하위 배열(EMP)의 개수만큼 행이 늘어난 것을 확인할 수 있다.

 

DEFAULT 옵션 사용법

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

 

 

DEFAULT [기본 값] ON EMPTY 옵션을 사용하면 해당 키 값을 가져올 때 값이 비어있는 경우 기본 값을 표시한다.

 

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 ERROR    
            )
       ) jt

 

 

DEFAULT [기본 값] ON  ERROR 옵션을 사용하면 해당 키 값을 가져올 때 ERROR가 발생한  경우 기본 값을 표시한다.

 

ON EMPTY 옵션 사용법

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' NULL ON EMPTY  
            )
       ) jt

 

 

NULL ON EMPTY 옵션을 사용하여 해당 키 값을 가져올 때 값이 비어있는 경우 NULL을 표시한다.

 

아래의 예제처럼 각각의 칼럼별로 옵션을 지정할 수 있다.

empno NUMBER PATH '$.EMPNO'  NULL ON EMPTY,
ename VARCHAR2(10) PATH '$.ENAME'  NULL ON EMPTY,
sal   NUMBER PATH '$.SAL' NULL ON EMPTY

 

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' ERROR ON EMPTY  
            )
       ) jt

 

 

ERROR ON EMPTY 옵션을 사용하여 해당 키 값을 가져올 때 값이 비어있는 경우 ERROR를 발생시킨다.

 

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' NULL ON ERROR  
            )
       ) jt

 

 

NULL ON EMPTY 옵션을 사용하여 해당 키 값을 가져올 때 ERROR가 발생한 경우 NULL을 표시한다.

 

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' ERROR ON ERROR  
            )
       ) jt

 

 

ERROR ON EMPTY 옵션을 사용하여 해당 키 값을 가져올 때 ERROR가 발생한 경우 ERROR를 발생시킨다.

 

JSON 전체 칼럼에 옵션 적용법

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

 

 

시작점 경로 부분에 옵션을 지정하면 COLUMS에 지정된 전체 칼럼에 옵션이 적용된다.

 

저작자표시

'데이터베이스/오라클' 관련 글

더 보기

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

  • 2025.01.05
더 읽기

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

  • 2024.12.30
더 읽기

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

  • 2024.12.29
더 읽기

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

  • 2024.12.28
더 읽기

 

댓글

Copyright © 젠트의 프로그래밍 세상 All Rights Reserved

Designed by JB FACTORY

티스토리툴바