Oracle JSON 파싱

 회사에서 주로 사용하는 데이터 가운데 json으로 저장된 데이터를 분석하는 경우가 종종 존재한다.

 

이러한 분석을 할 때, 가장 힘들었던 부분은 자료가 인코딩되어 있던 것을 디코딩하는 것이었다. 처음에는 파이썬으로 분석을 처리하다가, 나중에는 오라클로 한번에 마무리하는 방식으로 변경하면서 처리했다. json이 저장된 컬럼은 clob 타입으로 되어 있어서, 오라클 VARCHAR 길이 제한 4,000을 넘어설 수는 없다. 그래서 아래와 같이 디코딩한 자료를 4000으로 잘라서 처리를 해야 에러가 나지 않는다.

 

 그리고 나서 추가된 부분을 수정하면서 느꼈던 부분은 배열 형태로 가져오는 것을 위해서 json_query()를 사용했다는 것이다.

 

SELECT JSON_VALUE(REPLACE(SYS.UTL_ENCODE.TEXT_DECODE(SUBSTR(json_encoding_column, 'UTF8', 1, 4000), '""', ''''), '$.tolCondNm') AS CONV_1, /*일반적인 json 형태의 자료를 가져올 때에는 json_value를 사용한다.*/

JSON_QUERY(REPLACE(SYS.UTL_ENCODE.TEXT_DECODE(SUBSTR(json_encoding_column, 'UTF8', 1, 4000), '""', ''''), '$.tgtId') AS CONV_2 /*배열 형태로 된 자료를 가져오는 경우, json_query를 사용한다. */

FROM JSON_TBL;