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!