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;