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!

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!

Is JSON PLSQL like XSLT?

In my last ODTUG CodeTalk about PLSQL JSON an attendee asked if it was like XSLT. I’m not familiar with XSLT so I looked it up and found these definitions on the w3schools site.

  • XSL (eXtensible Stylesheet Language) is a styling language for XML
  • XSLT stands for XSL Transformations

To me this means that XSLT is passed some XML and it transforms it into some different XML. The PLSQL JSON functionality can do that for JSON data (amongst many other things).

Here is a simple example:

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> CREATE OR REPLACE FUNCTION xslt ( p_json CLOB )
 2                     RETURN CLOB IS
 3     v_object JSON_OBJECT_T;
 4   BEGIN
 5     v_object := JSON_OBJECT_T.PARSE(p_json);
 6     IF v_object.HAS('KEY1') THEN
 7       v_object.PUT('KEY1','V1');
 8       v_object.RENAME_KEY('KEY1','K1');
 9     END IF;
 10    RETURN(v_object.TO_CLOB);
 11  END;
 12 /
Function created.

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

JSON_COL             XSLT
-------------------- --------------------
{"KEY1":"VALUE1"}    {"K1":"V1"}

I hope this answers the attendee’s question.

Thanks for reading!