PLSQL and JSON and Recursion

JSON data can take on almost any structure of arrays, key value pairs and objects. It can even contain embedded arrays, key-value pairs and objects. Thus parsing it without knowing the structure up-front can be painful. One way to conquer that pain is with recursion which PLSQL is really good at.

My Daughter - What are you doing?
Me          - I'm blogging about recursion.
My Daughter - So you curse and then curse again?
Me          - Yes, I guess so.
My Daughter - You are weird.
Me          - And you shall be too...

As with all examples on this blog we first create a table and fill it with JSON, glorious JSON!

CREATE TABLE waldo
( waldo_id NUMBER NOT NULL PRIMARY KEY,
  waldo_json CLOB,
CONSTRAINT waldo_is_json
     CHECK ( waldo_json IS JSON ) );

-- One top level scalar value that is NOT Waldo
INSERT INTO waldo
VALUES(1,'{"Not Waldo":"Top"}');

-- One top level scalar value that IS Waldo
INSERT INTO waldo
VALUES(2,'{"Waldo":"Top"}');

-- An embedded JSON value that IS Waldo
INSERT INTO waldo
VALUES(3,'{"Peeps":{"Waldo":"First Peep"}}');

Next up a is a function checking the first JSON key-value pair to see if the value is Waldo.

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);

BEGIN

  v_object := JSON_OBJECT_T.PARSE(p_json);
  --
  -- Check if Waldo is anywhere in object
  -- and if it is then get it - if not then
  -- just bail
  --
  -- This works fine for a top level scalar 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;

  RETURN(v_ret_val);

END;

Here is the function in action.

SELECT waldo_id,
       find_waldo_key(waldo_json) waldo
  FROM waldo;
1 No Waldo Here
2 Top
3 No Waldo Here

The first two rows are exactly what we wanted. One not Waldo and one Waldo.  But what happened to the last row? Why No Waldo? It’s because the JSON in the third row of data is actually an object – not just simple key-value pair. How do we get past this problem? We simply call the function again!

The first function call will use this:
   {"Peeps":{"Waldo":"First Peep"}}
The second (recursive) function call uses this:
   {"Waldo":"First Peep"}

Here is the function with a recursive call for objects.

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);

BEGIN

  v_object := JSON_OBJECT_T.PARSE(p_json);
  v_keys := v_object.get_keys;
  v_type := v_object.get_type(v_keys(1));

  IF v_type = 'SCALAR' THEN

   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;

  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 now call the function.

SELECT waldo_id,
       find_waldo_key(waldo_json) waldo
  FROM waldo;
1 No Waldo Here
2 Top
3 First Peep

That looks better! I’ll expand on these examples in the next few posts.

Thanks for reading!