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!

PLSQL JSON Parsing 1

This post begins a series on parsing JSON using PL/SQL. It shows how the GET_KEYS and GET_TYPE functions are used to get a list of keys in a JSON structure and determine the type of data they store.

SQL> CREATE OR REPLACE PROCEDURE what_are_you
  2                           ( p_json CLOB ) IS
  3
  4    v_object   JSON_OBJECT_T;
  5    v_key_list JSON_KEY_LIST;
  6
  7  BEGIN
  8
  9    v_object := JSON_OBJECT_T.PARSE(p_json);
 10    IF v_object.is_object THEN
 11      v_key_list := v_object.get_keys;
 12      DBMS_OUTPUT.PUT_LINE('JSON Has '      ||
 13                           v_key_list.COUNT ||
 14                           ' Keys');
 15      FOR counter IN 1..v_key_list.COUNT LOOP
 16        DBMS_OUTPUT.PUT_LINE(v_key_list(counter) ||
 17                               ' which is a '    ||
 18                  v_object.get_type(v_key_list(counter)));
 19      END LOOP;
 20    END IF;
 21   
 22  END;
 23  /
Procedure created.
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    what_are_you(v_json); 
 15  END; 
 16  /
JSON Has 2 Keys
Name which is a SCALAR
Reason which is a ARRAY
PL/SQL procedure successfully completed.

Note that only the top level elements (Name and Reason) are shown in this example. In the next post I’ll show how to interrogate deeper.

Thanks for reading!

Error Handling JSON and PLSQL 2

More examples of how JSON ON_ERROR settings and PLQL Exception Handlers interact. Or don’t interact as the case may be.

The code below shows 3 ways to handle requesting a JSON key value that does not exist.

  1. Set the ON_ERROR behaviour to 0 so no error is thrown but the returned JSON is null
  2. Check if the key value exists before looking for it
  3. Set the ON_ERROR behaviour to 1 so an error is thrown and caught by a user defined exception

Let’s get right to the code…

SQL> run
 1 DECLARE
 2
 3   e_no_json EXCEPTION;
 4   PRAGMA EXCEPTION_INIT(e_no_json, -40565);
 5   v_json_object json_object_t;
 6   v_json_element json_element_t;
 7
 8   /*------------------------------------------------*/
 9   PROCEDURE reset IS
 10  /*------------------------------------------------*/
 11  BEGIN
 12    v_json_object := json_object_t('{"KEY":"VAL"}');
 13    v_json_object.on_error(0);
 14    v_json_element := v_json_object.get('Key');
 15  END;
 16
 17 BEGIN
 18
 19   reset;
 20   --
 21   -- ON_ERROR set to zero so error ignored
 22   --
 23   IF v_json_element IS NULL THEN
 24     DBMS_OUTPUT.PUT_LINE('NULL - Its Not There');
 25   END IF;
 26
 27   reset;
 28   --
 29   -- ON_ERROR set to zero so error ignored but
 30   -- we check for key first
 31   --
 32   IF v_json_object.has('Key') THEN
 33     v_json_element := v_json_object.get('Key');
 34   ELSE
 35     DBMS_OUTPUT.PUT_LINE('HAS - Its Not There');
 36   END IF;
 37
 38   reset;
 39   --
 40   -- ON_ERROR set to one so error ignored and
 41   -- exception handler fires
 42   --
 43   v_json_object.on_error(1);
 44   v_json_element := v_json_object.get('Key');
 45
 46 EXCEPTION
 47   WHEN e_no_json THEN
 48    DBMS_OUTPUT.PUT_LINE('ERR - Its Not There');
 49* END;
NULL - Its Not There
HAS - Its Not There
ERR - Its Not There

The exception handler is defined in the DECLARE section and used in the EXCEPTION handler. This makes the code a bit more self documenting.

Thanks for reading!