울면서개발하기
DB / ORACLE

Oracle JSON_TABLE 예제 — JSON 배열을 행과 열로 풀기

· 2026년 10월 25일 다시 씀

화면 그리드의 변경분을 JSON 배열 하나로 넘겨받아 DB 에서 한 번에 처리할 일이 있었다. JSON_TABLE 을 쓰면 JSON 을 인라인 뷰처럼 행과 열로 풀어서 일반 테이블처럼 SELECT·JOIN·INSERT ... SELECT 할 수 있다. 12c(12.1.0.2)부터 있는 함수이고 19c·23ai 에서 그대로 쓴다.

1. 기본: 객체 배열 → 행

SELECT A.YYYY, A.DEPT_CD, A.ITEM_CD, A.ROW_TYPE
  FROM JSON_TABLE(
           '[
              {"YYYY": "2022", "DEPT_CD": "10", "ITEM_CD": "11", "ROW_TYPE": "C"},
              {"YYYY": "2022", "DEPT_CD": "20", "ITEM_CD": "21", "ROW_TYPE": "R"},
              {"YYYY": "2022", "DEPT_CD": "20", "ITEM_CD": "21", "ROW_TYPE": "U"}
            ]'
         , '$[*]'
           COLUMNS (
               YYYY     VARCHAR2(4)  PATH '$.YYYY',
               DEPT_CD  VARCHAR2(10) PATH '$.DEPT_CD',
               ITEM_CD  VARCHAR2(10) PATH '$.ITEM_CD',
               ROW_TYPE VARCHAR2(1)  PATH '$.ROW_TYPE'
           )
       ) A
 ORDER BY A.YYYY, A.DEPT_CD, A.ITEM_CD;
YYYY DEPT_CD ITEM_CD ROW_TYPE
2022 10 11 C
2022 20 21 R
2022 20 21 U
  • 두 번째 인자 '$[*]' 가 행 경로다. $ 는 문서 전체, [*] 는 배열의 모든 요소. 요소 하나가 행 하나가 된다.
  • COLUMNS 안의 PATH 는 그 요소(행) 기준의 경로다.
  • 경로에 해당하는 값이 없거나 형 변환에 실패하면 기본적으로 오류 없이 NULL 이 된다(NULL ON EMPTY, NULL ON ERROR 가 기본값). 조용히 NULL 이 되는 게 싫으면 열마다 ERROR ON ERROR 를 붙인다.

실제로는 JSON 을 문자열 상수가 아니라 바인드 변수로 받는다. 4000 바이트를 넘을 수 있으면 CLOB 로 바인드한다.

INSERT INTO ITEM_PLAN (YYYY, DEPT_CD, ITEM_CD)
SELECT J.YYYY, J.DEPT_CD, J.ITEM_CD
  FROM JSON_TABLE(:JSON_DATA, '$[*]'
           COLUMNS (
               YYYY     VARCHAR2(4)  PATH '$.YYYY',
               DEPT_CD  VARCHAR2(10) PATH '$.DEPT_CD',
               ITEM_CD  VARCHAR2(10) PATH '$.ITEM_CD',
               ROW_TYPE VARCHAR2(1)  PATH '$.ROW_TYPE'
           )) J
 WHERE J.ROW_TYPE = 'C';

2. 짧게 쓰기 (18c 이상)

18c 부터는 열 이름만 적어도 된다. 자료형을 빼면 VARCHAR2(4000), PATH 를 빼면 '$.<열 이름>' 으로 처리된다.

SELECT *
  FROM JSON_TABLE('{"A":1, "B":2, "C":3}', '$' COLUMNS (A, B, C));
A B C
1 2 3

배열이면 행 경로만 '$[*]' 로 바꾼다. 비교할 값이 숫자면 자료형은 적어 주는 편이 낫다. 적지 않으면 VARCHAR2 라서 WHERE B = 5 가 문자열을 숫자로 바꿔 비교하게 된다.

SELECT *
  FROM JSON_TABLE('[{"A":1, "B":2, "C":3}, {"A":4, "B":5, "C":6}, {"A":7, "B":8, "C":9}]'
                , '$[*]' COLUMNS (A NUMBER, B NUMBER, C NUMBER))
 WHERE B = 5;
A B C
4 5 6

대소문자 함정

짧게 쓸 때 JSON 필드를 찾는 경로는 적은 그대로 대소문자를 구분한다. 큰따옴표 없이 적어도 마찬가지다. 반면 결과 열 이름은 평소처럼 대문자가 된다.

-- JSON 필드가 소문자 "a" 인데 COLUMNS (A) 로 적으면 '$.A' 를 찾으므로 NULL
SELECT * FROM JSON_TABLE('{"a":1}', '$' COLUMNS (A));   -- A: (null)
SELECT * FROM JSON_TABLE('{"a":1}', '$' COLUMNS (a));   -- A: 1  (열 이름은 A, 경로는 '$.a')

화면에서 넘어오는 JSON 은 보통 camelCase 라서, 짧게 쓰면 이 차이로 값이 NULL 이 되기 쉽다. 헷갈리면 PATH 를 적는다.

따옴표 없는 키

원래 글에서는 '{A:1, B:2, C:3}' 처럼 키에 따옴표 없이 썼는데도 잘 돌았다. Oracle 은 입력 JSON 을 기본적으로 느슨한(lax) 문법으로 읽어서, 자바스크립트 객체처럼 따옴표 없는 키도 받아 준다. 하지만 표준 JSON 은 아니므로 다른 시스템과 주고받는 데이터는 키에 큰따옴표를 쓴다.

3. 중첩 배열: NESTED PATH

주문 하나에 품목이 여러 개 달린 것처럼 배열 안에 배열이 있으면 NESTED PATH 로 부모 값과 함께 펼친다.

SELECT J.*
  FROM JSON_TABLE(
           '{"orderNo": "A001",
             "items": [{"code": "P1", "qty": 2},
                       {"code": "P2", "qty": 1}]}'
         , '$'
           COLUMNS (
               ORDER_NO VARCHAR2(10) PATH '$.orderNo',
               NESTED PATH '$.items[*]'
                   COLUMNS (
                       SEQ     FOR ORDINALITY,
                       ITEM_CD VARCHAR2(10) PATH '$.code',
                       QTY     NUMBER       PATH '$.qty'
                   )
           )
       ) J;
ORDER_NO SEQ ITEM_CD QTY
A001 1 P1 2
A001 2 P2 1

FOR ORDINALITY 는 배열 안의 순번(1부터)을 준다.

4. 테이블에 저장된 JSON 을 풀 때

JSON 을 컬럼에 저장해 두었다면 테이블과 나란히 적어서 행마다 푼다. JSON_TABLE 은 앞에 나온 테이블의 컬럼을 참조할 수 있다.

SELECT O.ORDER_ID, J.ITEM_CD, J.QTY
  FROM ORDERS O
     , JSON_TABLE(O.DOC, '$.items[*]'
           COLUMNS (
               ITEM_CD VARCHAR2(10) PATH '$.code',
               QTY     NUMBER       PATH '$.qty'
           )) J;

컬럼이 JSON 자료형(21c 이상)이거나 IS JSON 제약 조건이 걸린 컬럼이면 JSON_TABLE(O.DOC.items[*] COLUMNS (...)) 처럼 점 표기로도 쓸 수 있다.

참고

다시 쓰며 바로잡은 점

  • 짧은 열 정의(COLUMNS (A, B, C))는 18c 부터 되는 문법이고, 경로가 대소문자를 구분한다는 점을 덧붙였다.
  • 따옴표 없는 키({A:1})가 동작하는 이유(lax 문법)와 표준 JSON 이 아니라는 점을 적었다.
  • 숫자 비교 열은 자료형을 적도록 예제를 고쳤다.
  • NESTED PATH, 테이블 컬럼의 JSON 풀기, 바인드 변수로 받는 방법을 더했다. 업무 컬럼명은 일반 예시로 바꿨다.