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!