PLSQL JSON Parsing 3

The previous post in this series moved recursively through a JSON structure to list all keys and structure types. The code worked but the output was an ugly concatenated string. This post shows how PL/SQL table functions can make it more useful.

I’ll post the code separately. Here are the important snippets.

-- Create a database type (object) that defines
-- the rows to return
CREATE OR REPLACE TYPE v_vc2_t AS TABLE OF VARCHAR2(100);
/

-- set the return type of the function to the above object
CREATE OR REPLACE FUNCTION what_are_you
 ( p_json CLOB )
 RETURN v_vc2_t IS
...
 -- Assemble rows as the function executes
 v_ret_val.EXTEND;
 v_ret_val(v_ret_val.LAST) := 
       v_key_list(counter) || ' ' ||
       v_object.get_type(v_key_list(counter));
...
-- Return the set of assembled rows
 RETURN(v_ret_val);
...
END;
/
-- the function is run like this
DECLARE
 v_json CLOB;
BEGIN
 v_json := '{"Name" : "Marty",
 "Reason" : [ { "Medium" : "Movie",
 "Title" : "Madagascar",
 "Year" : 2005 },
 { "Medium" : "Movie",
 "Title" : "Madagascar Escape 2 Africa",
 "Year" : 2008 },
 { "Medium" : "Movie",
 "Title" : "Madagascar 3 : Europes Most Wanted",
 "Year" : 2012 } ] }';
 -- SELECT rows from the function and display them
 FOR x IN (
     SELECT *
       FROM TABLE(what_are_you(v_json)) ) LOOP
   DBMS_OUTPUT.PUT_LINE(x.column_value);
 END LOOP;
END;

And the results look like this:

Name SCALAR
Reason ARRAY
Medium SCALAR
Title SCALAR
Year SCALAR
Medium SCALAR
Title SCALAR
Year SCALAR
Medium SCALAR
Title SCALAR
Year SCALAR

That output looks a lot better and can be easily processed. The code is available here.

Thanks for reading!