PLSQL JSON Parsing 2

PLSQL can move recursively through a JSON structure to extract its values.

When the what_are_you function shown below encounters an OBJECT in an ARRAY it calls the what_are_you function again to move recursively through its values.

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

  v_object JSON_OBJECT_T;
  v_key_list JSON_KEY_LIST;
  v_ret_val VARCHAR2(1000);

  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

      -- get the key name
      v_ret_val := v_ret_val || 
      v_key_list(counter) || ':' ||
      v_object.get_type(v_key_list(counter)) || ':';

      --
      -- if the element is an array then recurs 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;
           BEGIN
             v_element := v_array.get(counter);
                          v_ret_val := v_ret_val ||
                   what_are_you(v_element.to_string);
           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;

Here is an example function call.

SQL> DECLARE
 2     v_json CLOB;
 3   BEGIN
 4     v_json := '{"Name" : "Marty",
 5                 "Reason" : [ { "Medium" : "Movie",
 6                 "Title" : "Madagascar",
 7                 "Year" : 2005 },
 8               { "Medium" : "Movie",
 9           "Title" : "Madagascar Escape 2 Africa",
 10                "Year" : 2008 },
 11              { "Medium" : "Movie",
 12   "Title" : "Madagascar 3 : Europes Most Wanted",
 13                "Year" : 2012 } ] }';
 14    DBMS_OUTPUT.PUT_LINE(what_are_you(v_json));
 15 END;
 16 /
Name:SCALAR:Reason:ARRAY:Medium:SCALAR:Title:SCALA
R:Year:SCALAR:Medium:SCALAR:Title:SCALAR:Year:SCAL
AR:Medium:SCALAR:Title:SCALAR:Year:SCALAR:

The results are not formatted very well. The next post presents ways to do that better.

Thanks for reading!