PLSQL JSON Parsing With Dataguides 1

Oracle JSON data includes a data guide which acts as a data dictionary. This simplifies parsing by providing element names, datatypes and position in the JSON tree. This post demonstrates using the dataguide and dynamic PLSQL to parse a simple JSON structure.

First up – here’s the table and JSON data used by the function.

SQL> CREATE TABLE json_data
 2       ( json_pk NUMBER NOT NULL PRIMARY KEY,
 3         json_col CLOB
 4         CONSTRAINT is_json CHECK ( json_col IS JSON ) );
Table created.
 
SQL> INSERT INTO json_data
 2   VALUES(1,
 3         '{"KEY1":"VALUE1"}');
1 row created.

SQL> INSERT INTO json_data
 2   VALUES(2,
 3         '{"KEY2":"VALUE2"}');
1 row created.

SQL> INSERT INTO json_data
 2   VALUES(3,
 3         '{"KEY2":"VALUE2","KEY3":"VALUE3"}');
1 row created.

And now for some code. This function accepts JSON data as an argument and returns the dataguide elements as text. We’ll review the contents of the dataguide below and then set about using them to extract values.

SQL> CREATE OR REPLACE FUNCTION parser ( p_json CLOB )
 2                     RETURN CLOB AS
 3
 4     v_dg CLOB;
 5     v_array JSON_ARRAY_T;
 6     v_element JSON_ELEMENT_T;
 7     v_ret_val VARCHAR2(1000);
 8
 9     e_no_more_elements EXCEPTION;
10     PRAGMA EXCEPTION_INIT(e_no_more_elements,-30625); 
11
12   BEGIN
13
14     -- Yes, I selected from DUAL. Sorry. 
15     -- I'll explain later. For now please just
16     -- roll with it...
17     SELECT JSON_DATAGUIDE(p_json)
18       INTO v_dg
19       FROM DUAL; 
20 
21     -- The dataguide is a JSON array so it loads 
22     -- nicely into a local JSON_ARRAY_T variable 
23     v_array := JSON_ARRAY_T(v_dg); 
24 
25     -- Loop through the elements and add them to the 
26     -- return string. Stop when we run out of elements 
27     -- and ORA-30625 is thrown. 
28     DECLARE 
29       v_counter NUMBER := 0; 
30     BEGIN 
31       LOOP 
32         v_element := v_array.get(v_counter); 
33         v_ret_val := v_ret_val || v_element.to_string; 
34         v_counter := v_counter + 1; 
35       END LOOP; 
36     EXCEPTION 
37       WHEN e_no_more_elements THEN 
38         NULL; 
39       WHEN OTHERS THEN 
40        RAISE; 
41     END; 
42 
43     -- send back the list of elements that defines 
44     -- the list of elements - its all about schema 
45     -- on demand (SOD). Never mind - I just made that up. 
46     RETURN(v_ret_val); 
47 
48   END; 
49  / 
Function created.

And here is an example call:

SQL> SELECT json_col,
 2          parser(json_col) pj
 3     FROM json_data;
JSON_COL                          PJ
--------------------------------- -------------------------
{"KEY1":"VALUE1"}                 {"o:path":"$.KEY1","type"
                                   :"string","o:length":8}
{"KEY2":"VALUE2"}                 {"o:path":"$.KEY2","type"
                                   :"string","o:length":8}
{"KEY2":"VALUE2","KEY3":"VALUE3"} {"o:path":"$.KEY2","type"
                                   :"string","o:length":8}
                                  {" o:path":"$.KEY3","type"
                                   :" strin 3 rows selected.

The PJ column contains the dataguide. Each element in the dataguide contains three key-value pairs:

⦁ o:path – the path within the JSON to find this element value
⦁ type – the datatype of the JSON value
⦁ o:length – the length of the element value

The o:path value makes it easy to find the actual value by providing a full path from the top. For example and o:path value of $.KEY1 points to an element name KEY1 at the top of the JSON structure. This can be passed to the JSON_VALUE PLSQL function like this:

 JSON_VALUE(p_json,'$.KEY1')

That JSON_VALUE call can be added to the parsing function like this:

DECLARE
  v_path CLOB;
  v_plsql VARCHAR2(100);
  v_value VARCHAR2(100);
BEGIN
  -- extract the element path
  v_path := JSON_VALUE(v_element.to_string,'$."o:path"');
  -- use dynamic PLSQL to call JSON_VALUE passing in the
  -- path and JSON data
  v_plsql := 'BEGIN :1 := JSON_VALUE(:2,''' || 
  v_path || '''); END;';
  EXECUTE IMMEDIATE v_plsql USING OUT v_value, p_json;
  -- add the extracted to the return string
  v_ret_val := v_ret_val || v_value || ':';
END;

That changes the functions return values to this:

SQL> SELECT json_col,
 2          parser(json_col) pj
 3    FROM json_data;
JSON_COL                          PJ
--------------------------------- --------------------
{"KEY1":"VALUE1"}                 VALUE1:
{"KEY2":"VALUE2"}                 VALUE2:
{"KEY2":"VALUE2","KEY3":"VALUE3"} VALUE2:VALUE3:

The string values are extracted from the JSON and returned as text.

I’ll work through more ways to parse JSON with PLSQL and dataguides in the next few posts.

Thanks for reading!