PLSQL and JSON and Recursion II

The previous post in this series demonstrated recursion to parse JSON data in PLSQL. It assumed the JSON passed in would start with key-value pairs. But JSON is not always that predictable. For example an array of key-value pairs could be passed in. Lets see what changes that requires.

First I’ll add 2 new rows to the Waldo table. Each new row contains an array of key-value pairs. Note the square brackets around the JSON data. This denotes an array.

After creating the data I’ll run the parsing function.

INSERT INTO waldo
VALUES(4,'[{"Waldo":"Elem1"},{"A":"B"},{"B":"C"}]');

INSERT INTO waldo
VALUES(5,'[{"A":"B"},{"Waldo":"Elem2"},{"B":"C"}]');

SELECT waldo_id,
       find_waldo_key(waldo_json) waldo
  FROM waldo;
 find_waldo_key(waldo_json) waldo
 *
 ERROR at line 2:
 ORA-40587: invalid JSON type
 ORA-06512: at "SYS.JSON_OBJECT_T", line 90
 ORA-06512: at "D.FIND_WALDO_KEY", line 13

And here is unlucky line 13…

 v_object := JSON_OBJECT_T.PARSE(p_json);

It tries to use JSON_OBJECT_T but it fails because JSON_OBJECT_T insists data be in key-value pairs. It would work fine if the data was this:

{"Waldo":"Elem1"},{"A":"B"},{"B":"C"}

But we added square brackets to make it an array.

So what do we change in the code? I’m glad you asked. The main change is to parse the input as JSON_ELEMENT_T because it is more forgiving and then interrogate the element to see what it is.

CREATE OR REPLACE FUNCTION find_waldo_key (p_json VARCHAR2)
                  RETURN VARCHAR2 IS

 v_object  JSON_OBJECT_T;
 v_waldo   VARCHAR2(100);
 v_ret_val VARCHAR2(100);
 v_keys    JSON_KEY_LIST;
 v_type    VARCHAR2(100);
 v_x       VARCHAR2(100);
 v_element JSON_ELEMENT_T;

BEGIN

 -- load up an element because it is not picky
 -- it will take anything
 v_element := JSON_ELEMENT_T.PARSE(p_json);

 -- if the element is a JSON object then load
 -- it as an object and parse it later
 IF v_element.IS_OBJECT THEN
   v_object := JSON_OBJECT_T.PARSE(p_json);

 -- if the element is an array then loop through
 -- it's elements and call the parsing function again
 ELSIF v_element.IS_ARRAY THEN

   DECLARE
     v_array  JSON_ARRAY_T;
     v_size   NUMBER;
     v_e      JSON_ELEMENT_T;
     v_string VARCHAR2(100);
 BEGIN
     v_array := JSON_ARRAY_T.PARSE(v_element.to_string);
     v_size := v_array.get_size;
     FOR counter IN 0..(v_size - 1) LOOP
       v_element := v_array.get(counter);
       v_string := find_waldo_key(v_element.to_string);
       IF v_string <> 'No Waldo Here' THEN
         RETURN(v_string);
       END IF;
    END LOOP;
 END;

ELSE

 -- Just in case we get here and need
 -- to add more handling later
 RETURN('What is it?');

END IF;

-- To simplify the demo I'm going to hardcode
-- processing to the first key
v_keys := v_object.get_keys;
v_type := v_object.get_type(v_keys(1));

-- if the element is a scalar...
IF v_type = 'SCALAR' THEN

  -- if the object contains a key named Waldo
  -- then return its value
  IF v_object.has('Waldo') THEN
    v_waldo := v_object.get_string('Waldo');
    v_ret_val := v_waldo;
  ELSE
    v_ret_val := 'No Waldo Here';
  END IF;

-- if the element is an object then send it to the
-- parse function
ELSIF v_type = 'OBJECT' THEN

  v_object := v_object.get_object(v_keys(1));
  v_x := find_waldo_key(v_object.TO_CLOB);
  v_ret_val := v_x;

END IF;

RETURN(v_ret_val);

END;

And here are the results (including rows from the previous post).

SELECT waldo_id,
       find_waldo_key(waldo_json) waldo
  FROM waldo;
 
WALDO_ID   WALDO
---------- ------------------------------
         1 No Waldo Here
         2 Top
         3 First Peep
         4 Elem1
         5 Elem2

That’s a lot of code. And it only handles a fraction of whats possible in JSON data. I fear that adding functionality to it will make things more complex than it’s worth.

What I really want is a way to ask the JSON what it’s structure is and then navigate through it using that information as a … wait for it … Data Guide.

In the next few posts I’ll show how to use Oracle Data Guide to make parsing much easier.

Thanks for reading!