A generic JSON generator using a config table

 The limitation of working with JSONs is that they can come in a variety of structures with multiple levels of nesting and a combination of JSON objects, JSON arrays and key-value pairs.

This makes parsing and creating JSONs with such varied structures a challenge.

The JSON creation can be simplified to some extent by creating a configuration table, storing the JSON data in a particular order and format by which it becomes easier to create the JSON.

Below is a sample JSON I have conjured which has multiple levels of nesting of arrays, objects and key-value pairs. This I felt would be as good as it gets. Considering the wide varieity of JSON types and structures, though it may not be a completely generic JSON creator, it may work for a majority of cases. And with a bit of tweaking of the code and handling the boundary conditions it should be a workable customized solution for any JSON.

Sample JSON

{
    "JsonList": {
        "JsonObj": {
            "objtest1": {
                "test1key1": "test1value1",
                "test1key2": "test1value2"
            },
            "ID": "1",
            "ObjName": "jsonParser",
            "objtest": {
                "key1": "value1",
                "objtest2": {
                    "test2key1": "test2value1",
                    "test2key2": "test2value2"
                },
                "key2": "value2"
            },
            "nestedarray": [
                "json1",
                "xml1"
            ]
        },
        "keyvaluetest": "markup",
        "projects": [
            "json",
            "xml"
        ]
    },
    "employees": [
        {
            "name": "Shelley,Higgins",
            "job": "Accounting Manager"
        },
        {
            "name": "William,Gietz",
            "job": "Public Accountant"
        }
    ]
}

With this JSON we now create the JSON table which will store the information for the levels of nesting, the order of each element, the type of each element, its relation with the parent key. Once we have tableized this information, we can build a fairly simple plsql algorithm to create the JSON.

Below is the convention and logic used to tablize this JSON data

CREATE TABLE JSON_MATRIX_CONFIG
(
JSON_LEVEL	NUMBER,
JSON_KEY	VARCHAR2(50 BYTE),
JSON_TYPE	VARCHAR2(50 BYTE),
JSON_VALUE	VARCHAR2(4000 BYTE),
PARENT_KEY	VARCHAR2(100 BYTE),
PARENT_JSON_TYPE VARCHAR2(50 BYTE),
LEVEL_SEQ	NUMBER,
JSON_SRL_NO	NUMBER
);

Below is the description and usage of each column in the table. 

JSON_LEVEL - will give the level of nesting of the JSON element in the JSON structure. 

JSON_KEY - is the key value of the element in the JSON. 

JSON_TYPE - will indicate if the current element in the JSON is an object, array or string type. JSON_VALUE - is the value of the data stored for the corresponding JSON key. 

PARENT_KEY - is the key of the JSON element above the current JSON element. PARENT_JSON_TYPE - will indicate if the parent element in relation to the current element in the JSON is an object, array or string type. 

LEVEL_SEQ - At a given level, if there are multiple elements, it will indicate the sequence of the element at that level. 

JSON_SRL_NO - is the sequence of the element in the overall JSON structure. 

Below I will explain each column value with examples for some of the JSON elements. 

Consider the element "JsonList". It is the first element in the JSON at the highest level. So for this it has

JSON_LEVEL - 1. 

JSON_KEY - It is the object key  for the JSON object it contains. It is the element "JsonList".

JSON_TYPE - Its type is object. 

JSON_VALUE - Its value when creating the config table will be null. It will get populated when we call the JSON generation procedure.

PARENT_KEY - Since it is the first element its parent key will be NULL.

PARENT_JSON_TYPE - Since it is the first element its parent JSON type will be NULL.

LEVEL_SEQ - 1. There are 2 elements at this level. "JsonList" and "employees". "JsonList" is first in the sequence, so it will have a value 1 and "employees" will have a value of 2.

JSON_SRL_NO - 1. As it is the first element in the JSON.

Consider the element "objtest". 

JSON_LEVEL - 3. Considering the levels of nesting in the JSON, "objtest" is at the 3rd level of nesting.

JSON_KEY - It is the object key  for the JSON object it contains. It is the element  "objtest"

JSON_TYPE - Its type is object. 

JSON_VALUE - Its value when creating the config table will be null. It will get populated when we call the JSON generation procedure. It will get populated with the JSON object it contains.

PARENT_KEY - The key of the immediate parent holding this key. i.e. - "JsonObj"

PARENT_JSON_TYPE - The type of the immediate praent. i.e. - the type of  "JsonObj" is object.

LEVEL_SEQ - 4. There are 5 elements at this level. "objtest1", "ID","ObjName","objtest" and "nestedarray" under the parent key "JsonObj". In this JSON, at LEVEL 3, "objtest" is at the 4th sequence.

JSON_SRL_NO - 8. If we traverse the JSON sequentially you will see that this element is in the 8th position.

Consider the element "nestedarray". 

JSON_LEVEL - 3. Considering the levels of nesting in the JSON, "nestedarray" is at the 3rd level of nesting. It is at the same level of nesting as "objtest".

JSON_KEY - It is the array key  for the JSON array it contains. It is the element  "nestedarray".

JSON_TYPE - Its type is array. 

JSON_VALUE - Its value when creating the config table will be null. It will get populated when we call the JSON generation procedure. It will get populated with the JSON array it contains.

PARENT_KEY - The key of the immediate parent holding this key. i.e. - "JsonObj"

PARENT_JSON_TYPE - The type of the immediate praent. i.e. - the type of  "JsonObj" is object.

LEVEL_SEQ - 3. There are 5 elements at this level. "objtest1", "ID","ObjName","objtest" and "nestedarray" under the parent key "JsonObj". In this JSON, at LEVEL 3, "nestedarray" is at the 3rd sequence.

JSON_SRL_NO - 14. If we traverse the JSON sequentially you will see that this element is in the 14th position.

Consider the element "keyvaluetest": "markup". 

JSON_LEVEL - 2. Considering the levels of nesting in the JSON, "keyvaluetest" is at the 2nd level of nesting. 

JSON_KEY - "keyvaluetest". It is the key of the key-value pair "keyvaluetest": "markup", which is part of the JSON object "JsonList". 

JSON_TYPE - Its type is string. 

JSON_VALUE - "markup". Its value is the corresponding value of the key-value pair "keyvaluetest": "markup" that is part of the object "JsonList". 

PARENT_KEY - The key of the immediate parent holding this key. i.e. - "JsonList".

PARENT_JSON_TYPE - The type of the immediate parent. i.e. - the type of  "JsonList" is object.

LEVEL_SEQ - 2. There are 3 elements at this level. "JsonObj", "keyvaluetest" and "projects" under the parent key " JsonList". In this JSON, at LEVEL 2, "keyvaluetest" is at the 2nd sequence.

JSON_SRL_NO - 17. If we traverse the JSON sequentially you will see that this element is in the 17th position.

Consider the element "json1. 

JSON_LEVEL - 4. Considering the levels of nesting in the JSON, "json1" is at the 4th level of nesting. 

JSON_KEY - null. It is an element of the JSON array "nestedarray". Its key is blank. 

JSON_VALUE - "json1". Its value is the element "json1" which is part of the array "nestedarray". 

PARENT_KEY - The key of the immediate parent holding this array element. i.e. - "nestedarray".

PARENT_JSON_TYPE - The type of the immediate parent. i.e. - the type of  "nestedarray" is array.

LEVEL_SEQ - 1. There are 2 elements at this level. "json1" and "xml1" under the parent key " nestedarray". In this JSON, at LEVEL 4, "json1" is at the 1st sequence.

JSON_SRL_NO - 15. If we traverse the JSON sequentially you will see that this element is in the 15th position.

Once we are clear how to populate all the columns of the config table for each element of the JSON, we can populate the CONFIG table for all the elements of the JSON.

The config table data for the same is as below.

JSON_LEVEL	JSON_KEY	JSON_TYPE	JSON_VALUE	PARENT_KEY	PARENT_JSON_TYPE	LEVEL_SEQ	JSON_SRL_NO
1	JsonList	object				1	1
2	JsonObj	object		JsonList	object	1	2
3	objtest1	object		JsonObj	object	1	3
4	test1key1	string	test1value1	objtest1	object	1	4
4	test1key2	string	test1value2	objtest1	object	2	5
3	ID	string	1	JsonObj	object	2	6
3	ObjName	string	jsonParser	JsonObj	object	3	7
3	objtest	object		JsonObj	object	4	8
4	key1	string	value1	objtest	object	1	9
4	objtest2	object		objtest	object	2	10
5	test2key1	string	test2value1	objtest2	object	1	11
5	test2key2	string	test2value2	objtest2	object	2	12
4	key2	string	value2	objtest	object	3	13
3	nestedarray	array		JsonObj	object	5	14
4		string	json1	nestedarray	array	1	15
4		string	xml1	nestedarray	array	2	16
2	keyvaluetest	string	markup	JsonList	object	2	17
2	projects	array		JsonList	object	3	18
3		string	json	projects	array	1	19
3		string	xml	projects	array	2	20
1	employees	array				2	21
2	name	string	Shelley,Higgins	employees	array	1	22
2	job	string	Accounting Manager	employees	array	1	23
2	name	string	William,Gietz	employees	array	2	24
2	job	string	Public Accountant	employees	array	2	25

The logic for generating the JSON is as below

We first find out the list of independent JSONs at LEVEL 1 which comprise the entire JSON structure.

For each of these JSONs, we get the list of all the parent keys.

For each parent key, we get the list of elements forming the JSON object or array and we create that JSON array/object and store it as an intermediate JSON.

We then combine all these JSONs to form the final JSON structure.

Below is the code for the same.

 

CREATE OR REPLACE PACKAGE json_traversal_generate
AS

PROCEDURE gnrt_json_from_base;

END;
/

The package body

CREATE OR REPLACE PACKAGE BODY json_traversal_generate
AS 

PROCEDURE gnrt_json_from_base
IS
--Cursor to select all level 1 JSONs
  CURSOR C0
  IS
    SELECT JSON_KEY, JSON_SRL_NO min_srl_no,
    (SELECT NVL(MIN(JSON_SRL_NO),999) FROM json_matrix_config_base b WHERE PARENT_KEY IS NULL AND b.JSON_SRL_NO > a.JSON_SRL_NO) max_srl_no,
    JSON_VALUE, JSON_TYPE
    FROM json_matrix_config_base a
    WHERE PARENT_KEY IS NULL
    ORDER BY LEVEL_SEQ;

--Cursor to select parent keys for these JSONs
  CURSOR C1 (min_srl_no NUMBER, max_srl_no  NUMBER)
  IS
    SELECT DISTINCT JSON_LEVEL, PARENT_KEY, JSON_TYPE, JSON_SRL_NO
    FROM json_matrix_config_base
    WHERE JSON_SRL_NO >= min_srl_no AND JSON_SRL_NO < max_srl_no
    ORDER BY JSON_LEVEL DESC, JSON_SRL_NO;

--Cursor to select the key-value pairs in each parent JSON    
  CURSOR C2(p_parent_key   VARCHAR2)
  IS
    SELECT PARENT_KEY, PARENT_JSON_TYPE, JSON_KEY, JSON_VALUE, JSON_TYPE, LEVEL_SEQ
    FROM json_matrix_config_base
    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();
    jo_temp_final        JSON_OBJECT_T := JSON_OBJECT_T();
    l_arr JSON_ARRAY_T := JSON_ARRAY_T();
    l_arr_1 JSON_ARRAY_T := JSON_ARRAY_T();
    l_arr_final JSON_ARRAY_T := JSON_ARRAY_T();
    tmp_obj_str    CLOB; --VARCHAR2(4000);
    tmp_arr_str    VARCHAR2(4000);
    tmp_json_key    VARCHAR2(100);
    prev_level_seq  NUMBER := 0;
    key_value   BOOLEAN := FALSE;
BEGIN
  FOR rec_srl IN C0 -- Fetch each LEVEL 1 JSON in the structure
  LOOP --{
    FOR pkey IN C1(rec_srl.min_srl_no, rec_srl.max_srl_no)
    LOOP -- get the parent keys for each JSON
        jo_temp := JSON_OBJECT_T();
        jo_temp_1 := JSON_OBJECT_T();
        l_arr := JSON_ARRAY_T();
        prev_level_seq := 0;
        key_value := FALSE;

        FOR rec IN C2(pkey.PARENT_KEY)
        LOOP --get the individual elements for each parent JSON
           IF rec.JSON_TYPE = 'object' -- If it is an object add each element to that object 
           THEN
              jo_temp_1 := JSON_OBJECT_T(rec.JSON_VALUE);
              jo_temp.put(rec.JSON_KEY,jo_temp_1);
           ELSIF rec.JSON_TYPE = 'array' -- If it is an array add each element to that array

           THEN
              l_arr_1 := JSON_ARRAY_T(rec.JSON_VALUE);
              jo_temp.put(rec.JSON_KEY,l_arr_1);              
           ELSIF rec.JSON_TYPE = 'string'
           THEN
              IF rec.JSON_KEY IS NULL
              THEN
                  l_arr.append(rec.JSON_VALUE);  -- it is a simple JSON array of strings
              ELSE
                  key_value := TRUE;
                  IF prev_level_seq <> 0 AND prev_level_seq <> rec.LEVEL_SEQ AND rec.PARENT_JSON_TYPE = 'array'-- it is an object array. append each temp json object to the json array
                  THEN
--                    dbms_output.put_line ('prev_level_seq ' || prev_level_seq);
--                    dbms_output.put_line ('jo_temp ' || jo_temp.to_string);
                    l_arr.append(jo_temp);  
                    jo_temp := JSON_OBJECT_T();
                  END IF;  
                  jo_temp.put(rec.JSON_KEY, rec.JSON_VALUE); -- add the key value pair of the json object to a temp object.
--                dbms_output.put_line('jo_temp in loop' || jo_temp.to_string);
              END IF;
          END IF;
          prev_level_seq := rec.LEVEL_SEQ;
        END LOOP;
        
        IF NOT l_arr.IS_NULL
        THEN  
          IF key_value = TRUE  
          THEN
            l_arr.append(jo_temp); --add the last json object to the array.  
          END IF;
          tmp_arr_str := l_arr.to_string;
         UPDATE json_matrix_config_base a --update the intermediate json array in JSON_VALUE column for that array key.
          SET JSON_VALUE = tmp_arr_str
          WHERE JSON_KEY = pkey.PARENT_KEY
            AND JSON_TYPE = 'array'
            AND LEVEL_SEQ = (
              SELECT MAX(LEVEL_SEQ)
              FROM json_matrix_config_base b
              WHERE JSON_KEY = pkey.PARENT_KEY
                AND JSON_TYPE = 'array')
             AND JSON_LEVEL = pkey.JSON_LEVEL - 1;
        END IF;
        
--        dbms_output.put_line('temp json ' || jo_temp.to_string);
        tmp_obj_str := jo_temp.to_string;
        tmp_obj_str := TRIM (BOTH '"' FROM (tmp_obj_str));
        UPDATE json_matrix_config_base a --update the intermediate json object in JSON_VALUE column for that array key.

        SET JSON_VALUE = tmp_obj_str
        WHERE JSON_KEY = pkey.PARENT_KEY
          AND JSON_TYPE = 'object'
          AND LEVEL_SEQ = (
            SELECT MAX(LEVEL_SEQ)
            FROM json_matrix_config_base b
            WHERE JSON_KEY = pkey.PARENT_KEY
              AND JSON_TYPE = 'object')
           AND JSON_LEVEL = pkey.JSON_LEVEL - 1;  
           
    END LOOP;
       
--We then finally add each JSON object to the final JSON.        
      IF rec_srl.JSON_TYPE = 'object'
      THEN
--       dbms_output.put_line('final object json str ' || tmp_obj_str);
        SELECT JSON_VALUE
        INTO tmp_obj_str
        FROM json_matrix_config_base
        WHERE PARENT_KEY IS NULL
        AND JSON_TYPE = rec_srl.JSON_TYPE
        AND JSON_KEY = rec_srl.JSON_KEY;
       tmp_obj_str := TRIM (BOTH '"' FROM (tmp_obj_str));
       jo_temp_1 := JSON_OBJECT_T(tmp_obj_str);

          jo_temp_final.put(rec_srl.JSON_KEY,jo_temp_1);
      END IF;

--We then finally add each JSON array to the final JSON.      
      IF rec_srl.JSON_TYPE = 'array'
      THEN
        SELECT JSON_VALUE
        INTO tmp_arr_str
        FROM json_matrix_config_base
        WHERE PARENT_KEY IS NULL
        AND JSON_TYPE = rec_srl.JSON_TYPE
        AND JSON_KEY = rec_srl.JSON_KEY; 

       tmp_arr_str := rec_srl.JSON_VALUE;
       tmp_arr_str := TRIM (BOTH '"' FROM (tmp_arr_str));

       l_arr_1 := JSON_ARRAY_T(tmp_arr_str);
       jo_temp_final.put(rec_srl.JSON_KEY,l_arr_1);
      END IF;
--    dbms_output.put_line('final final json ' || jo_temp_final.to_string);
  END LOOP; --} end C0
--This will display the final JSON which is created
  dbms_output.put_line('final json ' || jo_temp_final.to_string);

END;
END json_traversal_generate;
/

BEGIN
   json_traversal_generate.gnrt_json_from_base;  
END;

{
    "JsonList": {
        "JsonObj": {
            "objtest1": {
                "test1key1": "test1value1",
                "test1key2": "test1value2"
            },
            "ID": "1",
            "ObjName": "jsonParser",
            "objtest": {
                "key1": "value1",
                "objtest2": {
                    "test2key1": "test2value1",
                    "test2key2": "test2value2"
                },
                "key2": "value2"
            },
            "nestedarray": [
                "json1",
                "xml1"
            ]
        },
        "keyvaluetest": "markup",
        "projects": [
            "json",
            "xml"
        ]
    },
    "employees": [
        {
            "name": "Shelley,Higgins",
            "job": "Accounting Manager"
        },
        {
            "name": "William,Gietz",
            "job": "Public Accountant"
        }
    ]
}

The JSON generated from this configuration setup is of the same structure from which the configuration table was created.

The final json table with the values of intermediate JSONs created will be as below

JSON_LEVEL	JSON_KEY	JSON_TYPE	JSON_VALUE	PARENT_KEY	PARENT_JSON_TYPE	LEVEL_SEQ	JSON_SRL_NO
1	JsonList	object	{"JsonObj":{"objtest1":{"test1key1":"test1value1","test1key2":"test1value2"},"ID":"1","ObjName":"jsonParser","objtest":{"key1":"value1","objtest2":{"test2key1":"test2value1","test2key2":"test2value2"},"key2":"value2"},"nestedarray":["json1","xml1"]},"keyvaluetest":"markup","projects":["json","xml"]}			1	1
2	JsonObj	object	{"objtest1":{"test1key1":"test1value1","test1key2":"test1value2"},"ID":"1","ObjName":"jsonParser","objtest":{"key1":"value1","objtest2":{"test2key1":"test2value1","test2key2":"test2value2"},"key2":"value2"},"nestedarray":["json1","xml1"]}	JsonList	object	1	2
3	objtest1	object	{"test1key1":"test1value1","test1key2":"test1value2"}	JsonObj	object	1	3
4	test1key1	string	test1value1	objtest1	object	1	4
4	test1key2	string	test1value2	objtest1	object	2	5
3	ID	string	1	JsonObj	object	2	6
3	ObjName	string	jsonParser	JsonObj	object	3	7
3	objtest	object	{"key1":"value1","objtest2":{"test2key1":"test2value1","test2key2":"test2value2"},"key2":"value2"}	JsonObj	object	4	8
4	key1	string	value1	objtest	object	1	9
4	objtest2	object	{"test2key1":"test2value1","test2key2":"test2value2"}	objtest	object	2	10
5	test2key1	string	test2value1	objtest2	object	1	11
5	test2key2	string	test2value2	objtest2	object	2	12
4	key2	string	value2	objtest	object	3	13
3	nestedarray	array	["json1","xml1"]	JsonObj	object	5	14
4		string	json1	nestedarray	array	1	15
4		string	xml1	nestedarray	array	2	16
2	keyvaluetest	string	markup	JsonList	object	2	17
2	projects	array	["json","xml"]	JsonList	object	3	18
3		string	json	projects	array	1	19
3		string	xml	projects	array	2	20
1	employees	array	[{"name":"Shelley,Higgins","job":"Accounting Manager"},{"name":"William,Gietz","job":"Public Accountant"}]			2	21
2	name	string	Shelley,Higgins	employees	array	1	22
2	job	string	Accounting Manager	employees	array	1	23
2	name	string	William,Gietz	employees	array	2	24
2	job	string	Public Accountant	employees	array	2	25

God's Word for the day

Do not slight the discourse of the sages,

  but busy yourself with their maxims.

because from them you will learn discipline.

  and how to serve princes.

Do not ignore the discourse of the aged,

  for they themselves learned from their parents.

From them you learned how to understand

  and to give an answer when the need arises.

Sirach 8:8-9