[Oracle] Oracle 데이터를 Json 형태로 변환 [오라클 12c 이상]

• target:  Oracle 데이터를 Json 형태로 변환

 

---

• method: 

▶ 같이 보면 좋을 내용

 

https://pu3vig.tistory.com/120

[Oracle] Oracle 데이터를 Json 형태 문자열로 변환 [오라클 11g 이하]

 

https://pu3vig.tistory.com/121

[Java] CLOB 데이터 String 변환

https://pu3vig.tistory.com/126

[Mybatis] Oracle CLOB 데이터 조회

---

1. JSON_OBJECT [Oracle 12c release 2(12.1.0.2) 이상]

/*
- 테이블 명 : TB_COL
- 데이터 형식
> SELECT SEQ, ID, NAME, TYPE
FROM TB_COL;

>> Result
   SEQ     |      ID     |     NAME      |     TYPE
    1      |     COL1    |     컬럼1     |     TEXT
    2      |     COL2    |     컬럼2     |    NUMBER
    3      |     COL3    |     컬럼3     |     DATE
    4      |     COL1    |     컬럼4     |    DATETIME
*/

-- 위와 같은 ROW를 보유한 테이블이 존재하는 경우
SELECT SEQ
     , ID
     , NAME
     , TYPE
     , JSON_OBJECT(
            KEY 'SEQ' IS SEQ FORMAT JSON
          , KEY 'ID' IS ID FORMAT JSON
          , KEY 'NAME' IS NAME FORMAT JSON
          , KEY 'TYPE' IS TYPE FORMAT JSON
     ) AS ROW_INFO
  FROM TB_COL;

/*
>>
   SEQ     |      ID     |     NAME      |     TYPE    |  ROW_INFO
    1      |     COL1    |     컬럼1     |     TEXT    | {"SEQ":1,"ID":COL1,"NAME":컬럼1,"TYPE":TEXT}
    2      |     COL2    |     컬럼2     |    NUMBER   | {"SEQ":2,"ID":COL2,"NAME":컬럼2,"TYPE":NUMBER}
    3      |     COL3    |     컬럼3     |     DATE    | {"SEQ":3,"ID":COL3,"NAME":컬럼3,"TYPE":DATE}
    4      |     COL1    |     컬럼4     |   DATETIME  | {"SEQ":4,"ID":COL1,"NAME":컬럼4,"TYPE":DATETIME}
*/

 

---

2. JSON_ARRAY [Oracle 12c release 2(12.1.0.2) 이상]

/*
- 테이블 명 : TB_COL
- 데이터 형식
> SELECT SEQ, ID, NAME, TYPE
FROM TB_COL;

>> Result
   SEQ     |      ID     |     NAME      |     TYPE
    1      |     COL1    |     컬럼1     |     TEXT
    2      |     COL2    |     컬럼2     |    NUMBER
    3      |     COL3    |     컬럼3     |     DATE
    4      |     COL1    |     컬럼4     |    DATETIME
*/

-- 위와 같은 ROW를 보유한 테이블이 존재하는 경우
SELECT JSON_OBJECT(
            'ID' VALUE ID
          , 'ROW_INFO' VALUE JSON_ARRAY(NAME, TYPE)
     ) AS JSON_OBJ
  FROM TB_COL;

/*
>>
JSON_OBJ
{"ID":"COL1","ROW_INFO":["컬럼1","TEXT"]}
{"ID":"COL2","ROW_INFO":["컬럼2","NUMBER"]}
{"ID":"COL3","ROW_INFO":["컬럼3","DATE"]}
{"ID":"COL1","ROW_INFO":["컬럼4","DATETIME"]}
*/

---

3. JSON_OBJECTAGG [Oracle 12c release 2(12.1.0.2) 이상]

/*
- 테이블 명 : TB_COL
- 데이터 형식
> SELECT SEQ, ID, NAME, TYPE
FROM TB_COL;

>> Result
   SEQ     |      ID     |     NAME      |     TYPE
    1      |     COL1    |     컬럼1     |     TEXT
    2      |     COL2    |     컬럼2     |    NUMBER
    3      |     COL3    |     컬럼3     |     DATE
    4      |     COL1    |     컬럼4     |    DATETIME
*/

-- 위와 같은 ROW를 보유한 테이블이 존재하는 경우
SELECT ID
     , JSON_OBJECTAGG(
         KEY SEQ VALUE TYPE
     ) AS JSON_OBJAGG
  FROM TB_COL
 GROUP BY ID;

/*
>>
  ID  |  JSON_OBJAGG
 COL1 | {"1":"TEXT","4":"DATETIME"}
 COL2 | {"2":"NUMBER"}
 COL3 | {"3":"DATE"}
*/

---

4. JSON_ARRAYAGG [Oracle 12c release 2(12.1.0.2) 이상]

/*
- 테이블 명 : TB_COL
- 데이터 형식
> SELECT SEQ, ID, NAME, TYPE
FROM TB_COL;

>> Result
   SEQ     |      ID     |     NAME      |     TYPE
    1      |     COL1    |     컬럼1     |     TEXT
    2      |     COL2    |     컬럼2     |    NUMBER
    3      |     COL3    |     컬럼3     |     DATE
    4      |     COL1    |     컬럼4     |    DATETIME
*/

-- 위와 같은 ROW를 보유한 테이블이 존재하는 경우
SELECT ID
     , JSON_ARRAYAGG(TYPE ORDER BY ID) AS JSON_ARRAGG
  FROM TB_COL
 GROUP BY ID;

/*
>>
  ID  |  JSON_ARRAGG
 COL1 | ["TEXT","DATETIME"]
 COL2 | ["NUMBER"]
 COL3 | ["DATE"]
*/

---

5. JSON_OBJECT & JSON_ARRAYAGG

/*
- 테이블 명 : TB_COL
- 데이터 형식
> SELECT SEQ, ID, NAME, TYPE
FROM TB_COL;

>> Result
   SEQ     |      ID     |     NAME      |     TYPE
    1      |     COL1    |     컬럼1     |     TEXT
    2      |     COL2    |     컬럼2     |    NUMBER
    3      |     COL3    |     컬럼3     |     DATE
    4      |     COL1    |     컬럼4     |    DATETIME
*/

-- 위와 같은 ROW를 보유한 테이블이 존재하는 경우
SELECT ID
     , JSON_OBJECTAGG (
         'ID' VALUE ID
         , 'ROW_INFO' VALUE JSON_ARRAYAGG(JSON_OBJECT('SEQ' VALUE SEQ, KEY NAME VALUE TYPE ORDER BY SEQ) RETURNING CLOB)
     ) AS JSON_OBJ
  FROM TB_COL
 GROUP BY ID;

/*
>>
  ID  |  JSON_OBJ
 COL1 | {"ID":"COL1", "ROW_INFO": [{"SEQ":1, "컬럼1":"TEXT"}, {"SEQ":4, "컬럼4":"DATETIME"}]}
 COL2 | {"ID":"COL2", "ROW_INFO": [{"SEQ":2, "컬럼2":"NUMBER"}]}
 COL3 | {"ID":"COL3", "ROW_INFO": [{"SEQ":3, "컬럼3":"DATE"}]}
*/

 

---

• warning:

1. Oracle 12c Release 2(12.1.0.2) 이상 확인 필수

2. 하나의 컬럼에 4000 Byte 이상인 경우, RETURNING CLOB 적용

---

• source:

https://blog.naver.com/PostView.naver?blogId=pino93&logNo=222071912583

[Oracle] ORACLE 데이터를 JSON 형태로 변환 방법