Dont Make Assumptions

This post shows how to parse simple numeric JSON arrays using PLSQL functions. It’s a simple example code-wise but it demonstrate some important things to be aware of when parsing JSON data in PLSQL.

-- first I create a table to hold the JSON
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.

-- And insert a simple numeric array
SQL> INSERT INTO json_data
 2   VALUES(1,'{"KEY1":[0,1,2,3]}');
1 row created.

-- And here is the parser function to handle the array
SQL> CREATE OR REPLACE FUNCTION parser ( p_json CLOB )
  2                    RETURN VARCHAR2 AS
  3 
  4    v_object JSON_OBJECT_T;
  5    v_keys JSON_KEY_LIST;
  6    v_array JSON_ARRAY_T;
  7 
  8    v_ret_val VARCHAR2(100);
  9 
 10  BEGIN
 11 
 12    v_object := JSON_OBJECT_T.PARSE(p_json);
 13    v_keys := v_object.get_keys;
 14 
 15    v_array := v_object.get_array('KEY1');
 16 
 17    FOR counter IN 0..(v_array.get_size - 1) LOOP
 18       v_ret_val := v_ret_val || 
 19                      v_array.get_number(counter);
 20    END LOOP;
 21 
 22    RETURN(v_ret_val);
 23 
 24 END;
 25 /
Function created.

-- And now to run it
SQL> SELECT json_col,
  2         parser(json_col) pj
  3    FROM json_data;

JSON_COL           PJ
------------------ ------
{"KEY1":[0,1,2,3]} 0123

The function is straightforward:

  1. Load the object
  2. Determine the keys in the object
  3. Load the object as an array
  4. Loop through every element in the array and output the value

By using the GET_NUMBER function this code assumes the array values will be numeric.  What if an array of strings is passed in.

INSERT INTO json_data
VALUES(2,'{"KEY1":["A","B","C","D"]}');

SELECT json_col,
       parser(json_col) pj
  FROM json_data;
JSON_COL PJ
------------------------------ --------------------
{"KEY1":[0,1,2,3]}             0123
{"KEY1":["A","B","C","D"]}

The call to GET_NUMBER silently fails and returns NULL. To change the behaviour I add this line to the code.

 v_array.ON_ERROR(1);

This changes the output to:

SQL> SELECT json_col,
 2          parser(json_col) pj
 3     FROM json_data;

JSON_COL PJ
------------------------------ --------------------
{"KEY1":[0,1,2,3]} 0123
ERROR:
ORA-40566: JSON path expression selected a value of
           different data type.
ORA-06512: at "SYS.JDOM_T", line 424
ORA-06512: at "SYS.JSON_ARRAY_T", line 287
ORA-06512: at "D.PARSER", line 19

This makes the conversion failure obvious.

Another assumption the function made is that the array would be scalars. What if we passed a key-value pair to it?

{"KEY1":[0,1,2,3]}

This raises the following error because the array is empty so cannot be parsed. This error cannot be hidden.

ERROR: ORA-30625: method dispatch on NULL SELF argument is
       disallowed

In the next few articles I’ll demonstrate way to parse safely through JSON objects and avoid assumptions that may bite you.

Thanks for reading!

PLSQL JSON Parsing With Dataguides 2

The previous post demonstrated using a dataguide to parse scalar values. This post demonstrates using a dataguide to parse a simple JSON array.

SQL> -- put a simple numeric array into the demo table
SQL> INSERT INTO json_data
 2   VALUES(1,
 3          '{"KEY1":[0,1,2,3]}');
 1 row created.

Applying the parse function from the previous post reveals this result.

SQL> SELECT json_col,
 2          parser(json_col) pj
 3     FROM json_data;
 JSON_COL
 -------------------------------------------------
 PJ
 -------------------------------------------------
 {"KEY1":[0,1,2,3]}
 {"o:path":"$.KEY1","type":"array","o:length":16}

The important fields in the dataguide entry for the array are:

  • o:path is the path starting from the top of the JSON structure.
  • type is arrray

Next we’ll add code to the parser function to extract the dataguide elements.

-- {"o:path":"$.KEY1","type":"array","o:length":16}
DECLARE

  v_path CLOB;
  v_type VARCHAR2(30);

BEGIN

  -- extract the element path
  v_path := JSON_VALUE(v_element.to_string,'$."o:path"'
             ERROR ON ERROR);

  -- extract the element type
  v_type := JSON_VALUE(v_element.to_string,'$.type'
             ERROR ON ERROR);

  IF v_type = 'array' THEN
    DBMS_OUTPUT.PUT_LINE(v_type);
    DBMS_OUTPUT.PUT_LINE(v_path);
  END IF;

END;

And here is what that displays when the parser is run.

array
$.KEY1

Now we know the name of the array element so we just need to add parsing.  Let’s jump out to SQL*Plus to work through the parsing algorithm.

-- Lets try with a call to JSON_VALUE
-- Returns null -- WTH?
BEGIN
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1') );
END;
PL/SQL procedure successfully completed.

-- Lets add ERROR ON ERROR to see what went wrong
BEGIN
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1'
                        ERROR ON ERROR) );
END;
BEGIN
*
ERROR at line 1:
ORA-40456: JSON_VALUE evaluated to non-scalar value
ORA-06512: at line 2

-- so its not a scalar - boo hoo.
-- give me all the values with a * then...
BEGIN
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[*]'
                        ERROR ON ERROR) );
END;
BEGIN
*
ERROR at line 1:
ORA-40470: JSON_VALUE evaluated to multiple values
ORA-06512: at line 2

-- ok give me the values one at a time if you must...
-- ... but wait - how do i know when I'm done if there
-- is more or less than 4 values?
BEGIN
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[0]'
                        ERROR ON ERROR) );
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[1]'
                        ERROR ON ERROR) );
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[2]'
                        ERROR ON ERROR) );
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[3]'
                        ERROR ON ERROR) );
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[4]'
                        ERROR ON ERROR) );
END;
0
1
2
3
BEGIN
*
ERROR at line 1:
ORA-40462: JSON_VALUE evaluated to no value
ORA-06512: at line 14

-- I can just trap the exception
-- ORA-40462 equates to I AM DONE!
-- please forgive me for not defining an exception
-- just now I'll do that at the end...
BEGIN
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[0]'
                        ERROR ON ERROR) );
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[1]'
                        ERROR ON ERROR) );
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[2]'
                        ERROR ON ERROR) );
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[3]'
                        ERROR ON ERROR) );
  DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                   '$.KEY1[4]'
                        ERROR ON ERROR) );
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -40462 THEN
      NULL;
    ELSE
      RAISE;
    END IF;
END;
0
1
2
3
PL/SQL procedure successfully completed.
 
-- That needs to be in a loop because the number of
-- entries is not known. Here goes...
DECLARE
  v_counter NUMBER := 0;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                                     '$.KEY1[v_counter]'
                          ERROR ON ERROR) );
    v_counter := v_counter + 1;
   END LOOP;
END;
DECLARE
*
ERROR at line 1:
ORA-40457: JSON path expression contained an invalid
array index
('$.KEY1[v_counter]')
ORA-06512: at line 5

-- Thats actually good news - because it shows everything
-- is fine syntactically -- except for the darned pointer 
-- It turns out we can just concatenate it all together
-- like this:
DECLARE
  v_counter NUMBER := 0;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                           '$.KEY1[' || v_counter || ']'
                          ERROR ON ERROR) );
    v_counter := v_counter + 1;
  END LOOP;
END;
0
1
2
3
DECLARE
*
ERROR at line 1:
ORA-40462: JSON_VALUE evaluated to no value
ORA-06512: at line 5

-- Now handle the exception and we have some rock
-- solid code!
DECLARE
  v_counter NUMBER := 0;
  e_we_are_done EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_we_are_done,-40462);
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE( JSON_VALUE('{"KEY1":[0,1,2,3]}',
                             '$.KEY1[' || v_counter || ']'
                          ERROR ON ERROR) );
    v_counter := v_counter + 1;
  END LOOP;
EXCEPTION
  WHEN e_we_are_done THEN
    DBMS_OUTPUT.PUT_LINE('We are done!');
  WHEN OTHERS THEN
    RAISE;
END;
0
1
2
3
We are done!
PL/SQL procedure successfully completed.

In the next post I’ll show how this can be rolled into the parse function.

Thanks for reading!

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!