A generic multi level JSON object parse and update script using PLSQL

 The JSON examples show earlier work for regular JSONs which do not have too many levels of nesting.

What if you have a JSON having multiple levels of nesting, and in each level you have sub objects and multiple key value pairs. In that case the example parsers described in the previous posts may not pass.

To resolve such multi level nested objects I have changed to approach for parsing and updating the JSON. In this approach I will parse through each layer of the object and insert the data for each layer in a table with its hierarchy of nesting and order of sequence in the json maintained. Once this data is tabulated, it will become easier to perform data manipulation operations on it. Once this data is modified as per the requirement, we can recreate the JSON in the same format, but with the modified data. This parser is developed for a JSON of objects and key-value pairs. It can be further enhanced to include JSONs having all types of elements - objects, arrays and key-value pairs.

We first create the table into which we insert the JSON information and data for each level of the nested JSON.

--DROP TABLE json_matrix;
CREATE TABLE json_matrix
(
json_level       NUMBER,         -- indicates the level of nesting of the json tag/element
parent_key       VARCHAR2(100),  -- the parent key for the json tag
json_key         VARCHAR2(50),   -- the json key
json_type        VARCHAR2(50),   -- indicates where the json element is an array/object/key-value pair
json_value       VARCHAR2(4000), -- the value of the particular json key
level_seq        NUMBER          -- indicates the sequence of the element in the json type. 
);

Below is a sample JSON object which is nested upto 3 levels.

{
        "JsonObj": {
            "ID": "1",
            "ObjName": "jsonParser",
            "objtest": {
                "key1": "value1",
                "key2": "value2"
            }
        },
        "keyvaluetest": "markup"
    }
}

We then traverse through through the JSON object. For each element we check if it is object, key-value pair and insert the known variable like the level of nesting, the json key , json value and the sequence of nesting for that key-value pair. To update the parent key for a given element, we update the json key of the immediate prior json level. Once we have this data we can update the required key-values pairs as per our requirement. We can there recreate the JSON in the format required by selecting from the JSON matrix table.

Below is the logic for the same.

create or replace PACKAGE json_traversal_update_traverse
AS

v_type        VARCHAR2(10);
traversal_cnt NUMBER := 0;
new_run  BOOLEAN := TRUE;

   TYPE object_array_key IS VARRAY(100) OF VARCHAR2(100);
   v_object_array_key object_array_key := object_array_key();
   
PROCEDURE PARSE_JSON(p_json_input CLOB);
PROCEDURE json_object_traversal(p_json_input CLOB);
PROCEDURE UPDATE_JSON_VALUE(p_key  VARCHAR2, p_level  NUMBER, p_new_value VARCHAR2);
PROCEDURE gnrt_json;

END;
/

create or replace PACKAGE BODY json_traversal_update_traverse
AS 

PROCEDURE UPDATE_JSON_VALUE(p_key  VARCHAR2, p_level  NUMBER, p_new_value VARCHAR2)
AS

BEGIN
  UPDATE JSON_MATRIX
  SET JSON_VALUE = p_new_value
  WHERE JSON_LEVEL = p_level
    AND JSON_KEY = p_key;

END;

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.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
    v_type := 'object';
    
    json_traversal_update_traverse.json_object_traversal(p_json_input);
--  ELSIF IS_JSON_TYPE(p_json_input) = 'array'
--  THEN
--    v_type := 'array';
--    ja_1 := JSON_ARRAY_T();   --initialize the new JSON array 
--    json_traversal_update_traverse.json_array_traversal(p_json_input);
  ELSE
    dbms_output.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);
   v_json_type     VARCHAR2(10);
   num_level_seq      NUMBER := 0;     
   nested_object_type   VARCHAR2(10);
 
BEGIN
      IF new_run = TRUE
      THEN
        traversal_cnt := 0;
        new_run := FALSE;
      END IF;  

      traversal_cnt := traversal_cnt + 1;
      dbms_output.put_line('json_object_traversal ' || traversal_cnt);
      
   -- 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)
   dbms_output.put_line('key_count ' || keys.COUNT);
   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;
--     dbms_output.put_line('key_string ' || key_string);
--     dbms_output.put_line('value_string ' || value_string);
        
     CASE 
       WHEN
          IS_JSON_TYPE(value_string) = 'object'
         THEN
              num_level_seq := num_level_seq + 1; 
              INSERT INTO json_matrix(json_level, json_key, json_type, level_seq) VALUES (traversal_cnt, key_string, 'object', num_level_seq);
              num_level_seq := 0;
              v_object_array_key.EXTEND;
              v_object_array_key(traversal_cnt) := key_string;
              JSON_OBJECT_TRAVERSAL(value_string);
  
         WHEN IS_JSON_TYPE(value_string) = 'string'
         THEN
          dbms_output.put_line('type string ' || key_string || ' ' || value_string);
            
          value_string := TRIM (BOTH '"' FROM (value_string));
          num_level_seq := num_level_seq + 1;
          INSERT INTO json_matrix(json_level, json_key, json_type, json_value, level_seq) VALUES (traversal_cnt, key_string, 'string', value_string, num_level_seq);
         

      END CASE;
      UPDATE JSON_MATRIX
        SET LEVEL_SEQ = i 
      WHERE JSON_LEVEL = traversal_cnt
        AND JSON_KEY = key_string;

   END LOOP;
     IF traversal_cnt > 1 
     THEN
    UPDATE JSON_MATRIX
        SET PARENT_KEY = v_object_array_key(traversal_cnt-1)
      WHERE JSON_LEVEL = traversal_cnt
        AND PARENT_KEY IS NULL;
     END IF;   
       
   traversal_cnt := traversal_cnt - 1;

      --TEMPORARY RESET TO TRUE
      IF traversal_cnt = 0
      THEN
        new_run := TRUE;
       END IF; 
END json_object_traversal;


PROCEDURE gnrt_json
IS
  CURSOR C1
  IS
    SELECT DISTINCT JSON_LEVEL, PARENT_KEY, JSON_TYPE
    FROM JSON_MATRIX
    ORDER BY JSON_LEVEL DESC;
    
  CURSOR C2(p_parent_key   VARCHAR2)
  IS
    SELECT PARENT_KEY, JSON_KEY, JSON_VALUE, JSON_TYPE
    FROM JSON_MATRIX
    WHERE PARENT_KEY = p_parent_key
    ORDER BY LEVEL_SEQ;
    jo_temp        JSON_OBJECT_T := JSON_OBJECT_T();
    jo_temp_1        JSON_OBJECT_T := JSON_OBJECT_T();
    tmp_json_str    VARCHAR2(4000);
    tmp_json_key    VARCHAR2(100);
BEGIN
    FOR pkey IN C1
    LOOP
        jo_temp := JSON_OBJECT_T();
        jo_temp_1 := JSON_OBJECT_T();
        FOR rec IN C2(pkey.PARENT_KEY)
        LOOP
           IF rec.JSON_TYPE = 'object'
           THEN
              jo_temp_1 := JSON_OBJECT_T(rec.JSON_VALUE);
              jo_temp.put(rec.JSON_KEY,jo_temp_1);
           ELSE 
              jo_temp.put(rec.JSON_KEY, rec.JSON_VALUE);
           END IF;
           
           
        END LOOP;
        dbms_output.put_line('temp json ' || jo_temp.to_string);
        tmp_json_str := jo_temp.to_string;
        tmp_json_str := TRIM (BOTH '"' FROM (tmp_json_str));
        UPDATE JSON_MATRIX a
        SET JSON_VALUE = tmp_json_str
        WHERE JSON_KEY = pkey.PARENT_KEY
          AND JSON_TYPE = 'object'
          AND LEVEL_SEQ = (
            SELECT MAX(LEVEL_SEQ)
            FROM JSON_MATRIX b
            WHERE JSON_KEY = pkey.PARENT_KEY
              AND JSON_TYPE = 'object')
           AND JSON_LEVEL = pkey.JSON_LEVEL - 1;  
           
        IF pkey.JSON_LEVEL = 1
        THEN
          jo_temp_1 := JSON_OBJECT_T();
          SELECT JSON_VALUE, JSON_KEY
          INTO tmp_json_str, tmp_json_key
          FROM JSON_MATRIX
          WHERE 
            JSON_LEVEL = 1
          AND JSON_TYPE = 'object'
          AND LEVEL_SEQ = 1;
          jo_temp_1 := JSON_OBJECT_T(tmp_json_str);
          jo_temp.put(tmp_json_key,jo_temp_1);
          dbms_output.put_line('final json ' || jo_temp.to_string);
        END IF;  
    END LOOP;
END;
END json_traversal_update_traverse;
/

To test this, we update the value of 

•    jsonParser to jsonParserUpdate for key ObjName which is nested at the 3rd level 
•    value1 to value101 for key key1 which is nested at the 4th level 
•    markup to markupupdate for key keyvaluetest which is nested at the 2nd level of the JSON.

To test this, we run the following block

DECLARE
  P_JSON_INPUT CLOB;
BEGIN
  P_JSON_INPUT := '{
    "JsonList": {
        "JsonObj": {
            "ID": "1",
            "ObjName": "jsonParser",
            "objtest": {
                "key1": "value1",
                "key2": "value2"
            }
        },
        "keyvaluetest": "markup"
    }
}';
  EXECUTE IMMEDIATE 'TRUNCATE TABLE JSON_MATRIX'; 
  JSON_TRAVERSAL_UPDATE_TRAVERSE.PARSE_JSON(
    P_JSON_INPUT => P_JSON_INPUT
  );
  
  JSON_TRAVERSAL_UPDATE_TRAVERSE.UPDATE_JSON_VALUE('ObjName', 3, 'jsonParserUpdate');
  JSON_TRAVERSAL_UPDATE_TRAVERSE.UPDATE_JSON_VALUE('key1', 4, 'value101');
  JSON_TRAVERSAL_UPDATE_TRAVERSE.UPDATE_JSON_VALUE('keyvaluetest', 2, 'markupupdate');

  JSON_TRAVERSAL_UPDATE_TRAVERSE.gnrt_json;
  COMMIT;
--rollback; 
END;  

The entry in JSON_MATRIX table after calling JSON_TRAVERSAL_UPDATE_TRAVERSE.PARSE_JSON is as below

JSON_LEVEL	PARENT_KEY	JSON_KEY	JSON_TYPE	JSON_VALUE	LEVEL_SEQ
1		JsonList	object		1
2	JsonList	JsonObj	object		1
2	JsonList	keyvaluetest	string	markup	2
3	JsonObj	objtest	object		3
3	JsonObj	ID	string	1	1
3	JsonObj	Name	string	jsonParser	2
4	objtest	key2	string	value2	2
4	objtest	key1	string	value1	1

The updated JSON is as below

{
    "JsonList": {
        "JsonObj": {
            "ID": "1",
            "ObjName": "jsonParserUpdate",
            "objtest": {
                "key1": "value101",
                "key2": "value2"
            }
        },
        "keyvaluetest": "markupupdate"
    }
}

I test it by adding one more layer of objects and key-value pairs as below

{
    "JsonList": {
        "JsonObj": {
			"objtest1": {
                "test1key1": "test1value1",
                "test1key2": "test1value2"
            },
            "ID": "1",
            "ObjName": "jsonParser",
            "objtest": {
                "key1": "value1",
				"objtest2": {
                "test2key1": "test2value1",
                "test2key2": "test2value2"
            },
                "key2": "value2"
            }
        },
        "keyvaluetest": "markup"
    }
}

DECLARE
  P_JSON_INPUT CLOB;
BEGIN
  P_JSON_INPUT := '{
    "JsonList": {
        "JsonObj": {
			"objtest1": {
                "test1key1": "test1value1",
                "test1key2": "test1value2"
            },
            "ID": "1",
            "ObjName": "jsonParser",
            "objtest": {
                "key1": "value1",
				"objtest2": {
                "test2key1": "test2value1",
                "test2key2": "test2value2"
            },
                "key2": "value2"
            }
        },
        "keyvaluetest": "markup"
    }
}';

  EXECUTE IMMEDIATE 'TRUNCATE TABLE JSON_MATRIX';
  JSON_TRAVERSAL_UPDATE_TRAVERSE.PARSE_JSON(
    P_JSON_INPUT => P_JSON_INPUT
  );
  
  JSON_TRAVERSAL_UPDATE_TRAVERSE.UPDATE_JSON_VALUE('ObjName', 3, 'jsonParserUpdate');
  JSON_TRAVERSAL_UPDATE_TRAVERSE.UPDATE_JSON_VALUE('key1', 4, 'value101');
  JSON_TRAVERSAL_UPDATE_TRAVERSE.UPDATE_JSON_VALUE('keyvaluetest', 2, 'markupupdate');
  JSON_TRAVERSAL_UPDATE_TRAVERSE.UPDATE_JSON_VALUE('test2key1', 5, 'test2value1update');
  JSON_TRAVERSAL_UPDATE_TRAVERSE.UPDATE_JSON_VALUE('test1key2', 4, 'test1value2update');
  JSON_TRAVERSAL_UPDATE_TRAVERSE.gnrt_json;
  COMMIT;
--rollback; 
END;
/

The entry in JSON_MATRIX table after calling JSON_TRAVERSAL_UPDATE_TRAVERSE.PARSE_JSON is as below

   

JSON_LEVEL	PARENT_KEY	JSON_KEY	JSON_TYPE	JSON_VALUE	LEVEL_SEQ
1		JsonList	object	{"JsonObj":{"objtest1":{"test1key1":"test1value1","test1key2":"test1value2update"},"ID":"1","ObjName":"jsonParserUpdate","objtest":{"key1":"value101","objtest2":{"test2key1":"test2value1update","test2key2":"test2value2"},"key2":"value2"}},"keyvaluetest":"markupupdate"}	1
2	JsonList	keyvaluetest	string	markupupdate	2
2	JsonList	JsonObj	object	{"objtest1":{"test1key1":"test1value1","test1key2":"test1value2update"},"ID":"1","ObjName":"jsonParserUpdate","objtest":{"key1":"value101","objtest2":{"test2key1":"test2value1update","test2key2":"test2value2"},"key2":"value2"}}	1
3	JsonObj	objtest1	object	{"test1key1":"test1value1","test1key2":"test1value2update"}	1
3	JsonObj	ObjName	string	jsonParserUpdate	3
3	JsonObj	objtest	object	{"key1":"value101","objtest2":{"test2key1":"test2value1update","test2key2":"test2value2"},"key2":"value2"}	4
3	JsonObj	ID	string	1	2
4	objtest	key2	string	value2	3
4	objtest	key1	string	value101	1
4	objtest	objtest2	object	{"test2key1":"test2value1update","test2key2":"test2value2"}	2
4	objtest1	test1key1	string	test1value1	1
4	objtest1	test1key2	string	test1value2update	2
5	objtest2	test2key1	string	test2value1update	1
5	objtest2	test2key2	string	test2value2	2

The final JSON generated is

{
    "JsonList": {
        "JsonObj": {
            "objtest1": {
                "test1key1": "test1value1",
                "test1key2": "test1value2update"
            },
            "ID": "1",
            "ObjName": "jsonParserUpdate",
            "objtest": {
                "key1": "value101",
                "objtest2": {
                    "test2key1": "test2value1update",
                    "test2key2": "test2value2"
                },
                "key2": "value2"
            }
        },
        "keyvaluetest": "markupupdate"
    }
}

God's Word for the day

Stretch out your hand to the poor.

  so that your blessing may be complete.

Give graciously to all the living.

  do not withhold kindness even from the dead.

Do not avoid those who weep.

  but mourn with those who mourn.

Do not hesitate to visit the sick

  because for such deeds you shall be loved.

In all you do remember the end of your life.

  and then you will never sin.

Sirach 7:32-36

To be continued ...