ORACLE - JSON To Key Value Pair Table

Question

Is there any way to obtain a table with key/value pairs from a CLOB Json Column?

The idea here is to get these values, on a dynamic way. Because the CLOB column does not always contain the same structure.

I've created a function that does this, however since it literally parses the json string, when we use it in a table with many records its very slow. And by very slow I mean like 2-5 records per second, i know it's terrible.

The Oracle tools (v.12c) do not provide a dynamic way to obtain the json tags/values, we have always to specify the paths.

I've been digging all around without any luck. Any thoughts?

When you say "CLOB column does not always contain the same structure" what sort of variation do you mean? — APC, Mar 09 '17 at 18:36
@APC I can expect a different JSON structure per row (in the worst case scenario). Because that column will be filled by various different sources. — bmvr, Mar 09 '17 at 19:58

score 3 · Accepted Answer · answered Apr 08 '17 at 04:40

12.2 contains a set of PL/SQL objects that can be used to build a DOM like structure of a JSON document. You can then extract key lists etc using methods on the objects. Look in the 12.2 doc for JSON_OBJECT_T, JSON_ARRAY_T etc which can be used like this..

SQL> create or replace type NV_PAIR_T as object (
  2    NAME  VARCHAR2(32),
  3    VALUE VARCHAR2(32)
  4  )
  5  /

Type created.

SQL> create or replace type NV_PAIR_TABLE as TABLE of NV_PAIR_T
  2  /

Type created.

SQL> create or replace function GET_KEY_VALUES(P_JSON_DOC VARCHAR2)
  2  return NV_PAIR_TABLE PIPELINED
  3  as
  4    JO JSON_OBJECT_T := JSON_OBJECT_T(P_JSON_DOC);
  5    JO_KEYS JSON_KEY_LIST := JO.get_keys();
  6  begin
  7
  8    for i in 1..JO_KEYS.count loop
  9      pipe row (NV_PAIR_T(JO_KEYS(i),JO.get_string(JO_KEYS(i))));
 10    end loop;
 11  end;
 12  /

Function created.

SQL> select *
  2   from TABLE(GET_KEY_VALUES('{"A":"AA", "B":"BB", "C":"CC"}'))
  3  /
A                                AA
B                                BB
C                                CC

SQL>

Does this help

Please give me an example of an object with nested objects. I currently don't have my computer near to me. But if this works I'll be very happy! — bmvr, Apr 08 '17 at 08:18

score 0 · Answer 2 · answered Apr 08 '17 at 17:38

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>

score 0 · Answer 3 · answered May 22 '22 at 14:55

I have below json not able to parse above function. I want to parse data tag value

   {
 "data": [
   {
     "6": {
       "value": "test1"
     },
     "7": {
       "value": "test2"
     },
     "8": {
       "value": ""
     },
     "9": {
       "value": 1
     },
     "10": {
       "value": "test5"
     },
     "11": {
       "value": ""
     }
   },
   {
     "6": {
       "value": "test2"
     },
     "7": {
       "value": "test3"
     },
     "8": {
       "value": ""
     },
     "9": {
       "value": 2
     },
     "10": {
       "value": "test5"
     },
     "11": {
       "value": ""
     }
   },
   {
     "6": {
       "value": "test1"
     },
     "7": {
       "value": "test2"
     },
     "8": {
       "value": ""
     },
     "9": {
       "value": 1
     },
     "10": {
       "value": "test5"
     },
     "11": {
       "value": ""
     }
   },
   {
     "6": {
       "value": "test2"
     },
     "7": {
       "value": "test3"
     },
     "8": {
       "value": ""
     },
     "9": {
       "value": 2
     },
     "10": {
       "value": "test5"
     },
     "11": {
       "value": ""
     }
   }
 ],
 "fields": [
   {
     "id": 9,
     "label": "COL1",
     "type": "numeric"
   },
   {
     "id": 6,
     "label": "COL2",
     "type": "text"
   },
   {
     "id": 7,
     "label": "COL3",
     "type": "text"
   },
   {
     "id": 8,
     "label": "COL4",
     "type": "text"
   },
   {
     "id": 10,
     "label": "COL5",
     "type": "text"
   },
   {
     "id": 11,
     "label": "COL_DATE6",
     "type": "date"
   }
 ],
 "metadata": {
   "numFields": 6,
   "numRecords": 4,
   "skip": 0,
   "totalRecords": 4
 }

}

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Sumit Sharma, May 23 '22 at 06:57

ORACLE - JSON To Key Value Pair Table

3 Answers3

Linked