A generic JSON parser with Oracle PL/SQL

To parse a JSON we must first understand a few key points

The basic elements of a JSON are 

• An array. Example - ["grape"]
• An Object Example - {"name":"Spider"}
• An array can again be

1.  an array of strings. Example - ["grape","orange","mango"]
2.  an array of objects. Example - 

  [
       {
          "name": "Shelley,Higgins",
          "job": "Accounting Manager"
       },
       {
          "name": "William,Gietz",
          "job": "Public Accountant"
       }
    ]

 3.  an array of string , array and object


[
"Stirfry", 
{"name":"Spider"}, 
"Mosquitos", 
  ["finger","toe","nose"]
]

• A Object of arrays and individual key-value pairs

{
    "department": "Accounting",
    "employees": [
        {
        "name": "Shelley,Higgins",
        "job": "Accounting Manager"
        },
        {
        "name": "William,Gietz",
        "job": "Public Accountant"
        }
    ]
}

With this information in hand we should be good to parse any JSON string.
The JSON parser below will work for any of these types of valid JSONS. 
For any other JSON structure additional testing and code modifications may have to be done.


For parsing through a JSON array we leverage the code provided by our friend and ready reference in PLSQL - Steven Feuerstein

Working With JSON Arrays in PL/SQL


A very useful handy reference for PLSQL programmers from the said author.










In the array traversal procedure, for each element in the array If the element is a string, print the string If the element is an object, call a procedure json_object_traversal to traverse the object. If the element is an array, recursively call json_array_traversal to loop through each element in the array. In the object traversal procedure, get the list of keys in the object. For each key in the object, check if the corresponding value is an array. If array then call json_array_traversal. Else print the corresponding key-value pair in the object.
PROCEDURE json_array_traversal ( 
   json_document_in   IN CLOB
   ) 
IS 
   l_array     json_array_t; 
   l_object    json_object_t; 
   l_keys      json_key_list; 
   l_element   json_element_t; 
BEGIN 
   l_array := json_array_t.parse (json_document_in); 
 
 --  put_line ('Traverse: ' || l_array.stringify ()); 
 
   FOR indx IN 0 .. l_array.get_size - 1 
   LOOP 
 --     put_line ('Index: ' || indx, level_in); 
 
      CASE 
         WHEN l_array.get (indx).is_string 
         THEN 
            put_line(l_array.get (indx).to_string || ' is a string'); 
         WHEN l_array.get (indx).is_object 
         THEN 
            
            l_object := TREAT (l_array.get (indx) AS json_object_t); 
            dbms_output.put_line(l_object.to_string || ' is an object');  
            l_keys := l_object.get_keys; 
            JSON_OBJECT_TRAVERSAL(l_object.to_string);

         WHEN l_array.get (indx).is_array 
         THEN 
            dbms_output.put_line(l_array.get (indx).stringify || ' is an array'); -- call json_array_traversal again  
            json_array_traversal ( 
               TREAT (l_array.get (indx) AS json_array_t).stringify ()); 
         ELSE 
            DBMS_OUTPUT.put_line ( 
               '*** No match for type on array index ' || indx); 
      END CASE; 
   END LOOP; 
END;
Below is the code for traversing through the JSON object.
PROCEDURE json_object_traversal(p_json_input CLOB)
AS
   jo             JSON_OBJECT_T;
   keys           JSON_KEY_LIST;
   key_string     VARCHAR2(1000);
   value_string   VARCHAR2(1000);

BEGIN

   -- Parse JSON object
      jo := JSON_OBJECT_T.parse(p_json_input);

   -- Retrieve keys of the main JSON object
   keys := jo.get_keys;
   
   -- Loop through the main keys (department, employees)
   FOR i IN 1..keys.COUNT LOOP
      key_string := keys(i);
      -- Retrieve the value for each key
      value_string := jo.get(key_string).to_string;
       IF IS_JSON_TYPE(value_string) = 'array'
        THEN
          json_array_traversal(value_string);
        ELSE
            DBMS_OUTPUT.put_line('Key: ' || key_string);
            DBMS_OUTPUT.put_line('Value: ' || value_string);
        END IF;
   END LOOP;
END json_object_traversal;
One point to note in this, is that json_array_traversal calls  json_object_traversal if the element is an object, and similarly json_object_traversal  cals json_array_traversal if the element is an array..
This is causing a cyclic dependency between json_object_traversal  and json_array_traversal.
This cyclic dependency means you are stuck in a perpetual loop of invalidation and recompilation and oracle automatically invalidates both the objects. Though this cyclic dependency may indicate a design flaw, in this case it looks unavoidable, considering the structure of JSONs where you can have arrays in an object and objects in an array.
To work around this cyclic dependency we can put both these procedures in a package.
We now have the logic to traverse through a JSON array and through a JSON object.
We now simply write a wrapper procedure above these called PARSE_JSON, which will check, if the input data is a JSON object, then it will call json_object_traversal and if it is a JSON array, then it will call json_array_traversal.


Below is the package specs and body for the generic JSON parser.

CREATE OR REPLACE PACKAGE json_traversal
AS
PROCEDURE PARSE_JSON(p_json_input CLOB);
PROCEDURE json_object_traversal(p_json_input CLOB);
PROCEDURE json_array_traversal ( 
   json_document_in   IN CLOB);
END;
/
CREATE OR REPLACE PACKAGE BODY json_traversal
AS 

FUNCTION IS_JSON_TYPE (p_str  CLOB)
RETURN VARCHAR2 IS
--  l_str            CLOB := '["grape","orange","mango","banana"]'; --'test string'; --'["grape","orange","mango","banana"]'; --'{"employee_id" : 100, "employee_name" : "Stephen King"}';
  l_arr            JSON_ARRAY_T;
  l_obj            JSON_OBJECT_T;
  json_type        VARCHAR2(10);
 
BEGIN
  BEGIN
    l_arr := JSON_ARRAY_T(p_str);
    json_type := 'array';
  EXCEPTION
    WHEN OTHERS THEN
    BEGIN
        l_obj := JSON_OBJECT_T(p_str);
        json_type := 'object';
    EXCEPTION
       WHEN OTHERS THEN 
           json_type := 'string';
    END;   
  END;
  
--  dbms_output.put_line('json_type ' || json_type || ' : ' ||  p_str);
  RETURN json_type;
END;

PROCEDURE PARSE_JSON(p_json_input CLOB)
AS 
BEGIN
  IF IS_JSON_TYPE(p_json_input) = 'object'
  THEN
    json_traversal.json_object_traversal(p_json_input);
  ELSIF IS_JSON_TYPE(p_json_input) = 'array'
  THEN
    json_traversal.json_array_traversal(p_json_input);
  ELSE
    put_line('json_type string : ' || p_json_input );
  END IF;
END PARSE_JSON;

PROCEDURE json_object_traversal(p_json_input CLOB)
AS
   jo             JSON_OBJECT_T;
   keys           JSON_KEY_LIST;
   key_string     VARCHAR2(1000);
   value_string   VARCHAR2(1000);

BEGIN

   -- Parse JSON object
      jo := JSON_OBJECT_T.parse(p_json_input);

   -- Retrieve keys of the main JSON object
   keys := jo.get_keys;
   
   -- Loop through the main keys (department, employees)
   FOR i IN 1..keys.COUNT LOOP
      key_string := keys(i);
      -- Retrieve the value for each key
      value_string := jo.get(key_string).to_string;
       IF IS_JSON_TYPE(value_string) = 'array'
        THEN
          json_array_traversal(value_string);
        ELSE
            DBMS_OUTPUT.put_line('Key: ' || key_string);
            DBMS_OUTPUT.put_line('Value: ' || value_string);
        END IF;
   END LOOP;
END json_object_traversal;


PROCEDURE json_array_traversal ( 
   json_document_in   IN CLOB
   ) 
IS 
   l_array     json_array_t; 
   l_object    json_object_t; 
   l_keys      json_key_list; 
   l_element   json_element_t; 
BEGIN 
   l_array := json_array_t.parse (json_document_in); 
 
 --  put_line ('Traverse: ' || l_array.stringify ()); 
 
   FOR indx IN 0 .. l_array.get_size - 1 
   LOOP 
 --     put_line ('Index: ' || indx, level_in); 
 
      CASE 
         WHEN l_array.get (indx).is_string 
         THEN 
            put_line(l_array.get (indx).to_string || ' is a string'); 
         WHEN l_array.get (indx).is_object 
         THEN 
            
            l_object := TREAT (l_array.get (indx) AS json_object_t); 
            dbms_output.put_line(l_object.to_string || ' is an object');  
            l_keys := l_object.get_keys; 
            JSON_OBJECT_TRAVERSAL(l_object.to_string);

         WHEN l_array.get (indx).is_array 
         THEN 
            dbms_output.put_line(l_array.get (indx).stringify || ' is an array'); -- call json_array_traversal again  
            json_array_traversal ( 
               TREAT (l_array.get (indx) AS json_array_t).stringify ()); 
         ELSE 
            DBMS_OUTPUT.put_line ( 
               '*** No match for type on array index ' || indx); 
      END CASE; 
   END LOOP; 
END;
END json_traversal;

We now test the code by inputting a few sample JSON input types

DECLARE
  P_JSON_INPUT CLOB;
BEGIN

  P_JSON_INPUT := '["Fruits", 
        {"name":"Apple"}, 
        "Mangoes", 
        ["Alphonso","Devgadh","Dussehri"]
       ]';

  PARSE_JSON(
    P_JSON_INPUT => P_JSON_INPUT
  );
--rollback; 
END;
/

Output of the same which will display each key-value pair of the object, each element of the array

"Fruits" is a string
{"name":"Apple"} is an object
Key: name
Value: "Apple"
"Mangoes" is a string
["Alphonso","Devgadh","Dussehri"] is an array
"Alphonso" is a string
"Devgadh" is a string
"Dussehri" is a string

We test another employee department JSON

DECLARE
  P_JSON_INPUT CLOB;
BEGIN

    P_JSON_INPUT := '{
    "department": "Accounting",
    "employees": [
       {
          "name": "Shelley,Higgins",
          "job": "Accounting Manager"
       },
       {
          "name": "William,Gietz",
          "job": "Public Accountant"
       }
    ]
}';

  PARSE_JSON(
    P_JSON_INPUT => P_JSON_INPUT
  );
--rollback; 
END;

Output is as below

Key: department
Value: "Accounting"
{"name":"Shelley,Higgins","job":"Accounting Manager"} is an object
Key: name
Value: "Shelley,Higgins"
Key: job
Value: "Accounting Manager"
{"name":"William,Gietz","job":"Public Accountant"} is an object
Key: name
Value: "William,Gietz"
Key: job
Value: "Public Accountant"

References 

A generic JSON parser with Oracle PL/SQL 

PL/SQL Object Types for JSON in Oracle Database 12c Release 2 (12.2) 

Thought for the day

Do not babble in the assembly of the elders,

  And do not repeat yourself when you pray.

Do not hate hard labor or farmwork, 

  Which was created by the most high.

Do not enroll in the ranks of sinners,

  remember that retribution does not delay.

Humble yourself to the utmost,

  For the punishment of the ungodly is fire and worms.

Sirach 7:14-17