oracle json to table,ORACLE - JSON To Key Value Pair Table

Here's a variant that will walk a nested structure.

SQL> drop FUNCTION PROCESS_JSON_DOCUMENT

2 /

Function dropped.

SQL> drop TYPE NV_PAIR_TABLE

2 /

Type dropped.

SQL> drop TYPE NV_PAIR_T

2 /

Type dropped.

SQL> create or replace TYPE NV_PAIR_T as object (

2 JSON_PATH VARCHAR2(4000),

3 VALUE VARCHAR2(4000)

4 )

5 /

Type created.

SQL> create or replace TYPE NV_PAIR_TABLE

2 as TABLE of NV_PAIR_T

3 /

Type created.

SQL> create or replace FUNCTION PROCESS_JSON_DOCUMENT(P_JSON_PATH VARCHAR2, P_JSON_DOCUMENT VARCHAR2)

2 return NV_PAIR_TABLE PIPELINED

3 as

4 V_JSON_OBJECT JSON_OBJECT_T := JSON_OBJECT_T(P_JSON_DOCUMENT);

5 V_KEY_LIST JSON_KEY_LIST := V_JSON_OBJECT.get_keys();

6 V_KEY_NAME VARCHAR2(4000);

7 V_JSON_PATH VARCHAR2(4000);

8 V_CHILD_DOCUMENT VARCHAR2(4000);

9 begin

10 for i in 1..V_KEY_LIST.count loop

11 V_KEY_NAME := V_KEY_LIST(i);

12 if (V_JSON_OBJECT.get_type(V_KEY_LIST(i)) <> 'OBJECT') then

13 pipe row (NV_PAIR_T(P_JSON_PATH || '.' || V_KEY_NAME,V_JSON_OBJECT.get_string(V_KEY_NAME)));

14 else

15 V_JSON_PATH := P_JSON_PATH || '.' || V_KEY_NAME;

16 V_CHILD_DOCUMENT := V_JSON_OBJECT.get_object(V_KEY_NAME).to_string();

17 for j in (select * from TABLE(PROCESS_JSON_DOCUMENT(V_JSON_PATH, V_CHILD_DOCUMENT))) loop

18 pipe row (NV_PAIR_T(J.JSON_PATH,J.VALUE));

19 end loop;

20 end if;

21 end loop;

22 end;

23 /

Function created.

SQL> column JSON_PATH format A32

SQL> column VALUE format A32

SQL> select *

2 from TABLE(PROCESS_JSON_DOCUMENT('$','{"A":"AA", "B":"BB", "C":"CC", "X" : {"A":"AA", "B":"BB", "C":"CC"}}'))

3 /

$.A AA

$.B BB

$.C CC

$.X.A AA

$.X.B BB

$.X.C CC

6 rows selected.

SQL>