PLSQL JSON Parsing – The Code

Here is the code for parsing JSON structure into column in rows of a “table”.

CREATE OR REPLACE TYPE v_vc2_t AS TABLE OF VARCHAR2(100);
/

CREATE OR REPLACE FUNCTION what_are_you
 ( p_json CLOB )
 RETURN v_vc2_t IS

 v_object JSON_OBJECT_T;
 v_key_list JSON_KEY_LIST;
 v_ret_val v_vc2_t := v_vc2_t();

 v_array JSON_ARRAY_T;

BEGIN

  v_object := JSON_OBJECT_T.PARSE(p_json);

  -- if its a valid JSON object..
  IF v_object.is_object THEN

    -- get number of keys
    v_key_list := v_object.get_keys;

    -- for every key...
    FOR counter IN 1..v_key_list.COUNT LOOP

      -- add key name and type to the return set
      v_ret_val.EXTEND;
      v_ret_val(v_ret_val.LAST) := 
      v_key_list(counter) || ' ' ||
      v_object.get_type(v_key_list(counter));

      --
      -- if the element is an array then recurse with each
      -- element in the array
      --
 IF v_object.get_type(v_key_list(counter)) = 'ARRAY' THEN

   v_array := v_object.get_array(v_key_list(counter));
   FOR counter IN 0..v_array.get_size LOOP
     IF v_array.get_type(counter) = 'OBJECT' THEN
       DECLARE
         v_element JSON_ELEMENT_T;
         v v_vc2_t := v_vc2_t();
       BEGIN
         v_element := v_array.get(counter);
         v := what_are_you(v_element.to_string);
         --
         -- add the results of the recursive call
         -- to the main result set
         --
         FOR counter IN 1..v.COUNT LOOP
           v_ret_val.EXTEND;
           v_ret_val(v_ret_val.LAST) := v(counter);
        END LOOP;
      END;
    END IF;
  END LOOP;
  END IF; -- element is an array

END LOOP; -- every key
END IF; -- valid json object
RETURN(v_ret_val);
END;
/

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 } ] }';
   FOR x IN (
     SELECT *
       FROM TABLE(what_are_you(v_json)) ) LOOP
           DBMS_OUTPUT.PUT_LINE(x.column_value);
   END LOOP;
END;

 

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!