[ORACLE] JSON_TABLE 예제

JSON_TABLE SELECT 1

SELECT A.YYYY      AS YYYY
     , A.ACNT_UNIT AS ACNT_UNIT
     , A.ACNT_FG   AS ACNT_FG
     , A.ROW_TYPE  AS ROW_TYPE
  FROM JSON_TABLE(
       /* $[*] = []*/
        '
        [{
            "YYYY"      : "2022"
           ,"ACNT_UNIT" : "10"
           ,"ACNT_FG"   : "11"
           ,"ROW_TYPE"  : "C"
        }, {
            "YYYY"      : "2022"
           ,"ACNT_UNIT" : "20"
           ,"ACNT_FG"   : "21"
           ,"ROW_TYPE"  : "R"
        }, {
            "YYYY"      : "2022"
           ,"ACNT_UNIT" : "20"
           ,"ACNT_FG"   : "21"
           ,"ROW_TYPE"  : "U"
        }]
        '
       ,'$[*]' COLUMNS ( 
                          YYYY      VARCHAR2(10) PATH '$.YYYY'
                        , ACNT_UNIT VARCHAR2(10) PATH '$.ACNT_UNIT'
                        , ACNT_FG   VARCHAR2(10) PATH '$.ACNT_FG'
                        , ROW_TYPE  VARCHAR2(10) PATH '$.ROW_TYPE'
                       )
       ) A 
ORDER BY 1, 2, 3;

 

결과 1

 

 

 

JSON_TABLE  SELECT 2

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

 

결과 2

 

 

 

JSON_TABLE  SELECT 3

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

 

결과 3

 

 

 

 

 

 

JSON Developer's Guide

 

JSON_TABLE() Function in Oracle

 

 

 

 

 

번외) 구분자로 분리하여 행으로 반환하는 TABLE FUNCTION