In my earlier post I have shown how to parse a JSON nested upto any level and update any tag in the JSON by parsing each element in the JSON. The limitation of the parser was that it was able to handle JSONs having only object type elements in it.
A generic multi level JSON object parse and update script using PLSQLIn this version of the parser, I have enhanced the parser to parse though an array of objects and an array of strings also. The script can parse a JSON nested upto multiple levels and can traverse though all types of JSON elements upto any level of nesting.
Again I mention that this parser and generator will not work for all types of JSONs. It is just an approach that I have come up with to parse and generate a JSON. In the process I have tried to make it flexible to parse and generate most types and shapes of JSONs. To make it a fool proof logic will involve a lot more coding and testing. It can be used as a base for someone to build on and enhance to make it more generic for all types of JSONs.
A question arises, If there are already built in oracle functions like JSON_MERGEPATCH, JSON_TRANSOFRM for updating JSONs, why parse and traverse a JSON.
One reason can be that if there are a large number of JSONs to be modified, then handling the same programatically through a well build JSON parsing and udpate logic will be faster.
This again needs to be analyzed on a case to case base.
For this, we modify the JSON_MATRIX table and add two more columns to it. The PARENT_JSON_TYPE and JSON_SRL_NO.
CREATE TABLE JSON_MATRIX_CONFIG
(
JSON_LEVEL NUMBER, -- indicates the level of nesting of the json tag/element
JSON_KEY VARCHAR2(50 BYTE), -- the json key
JSON_TYPE VARCHAR2(50 BYTE), -- indicates where the json element is an array/object/key-value pair
JSON_VALUE VARCHAR2(4000 BYTE), -- the value of the particular json key
PARENT_KEY VARCHAR2(100 BYTE), -- the parent key for the json tag
PARENT_JSON_TYPE VARCHAR2(50 BYTE), -- the parent json type for the json tag. It can be an object or an array
LEVEL_SEQ NUMBER, -- indicates the sequence of the element in the json type
JSON_SRL_NO NUMBER -- the sequential srl no of the tag in the JSON
);Below is a sample JSON which contains a combination of arrays and objects and multiple levels of nesting.
{
"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 logic is similar to that used in the earlier example given in the link above. It has been enhanced to traverse a JSON array. For each element it will check if the element is an object, an array or a key-value pair and insert the information like the level of nesting in JSON, the json key, the json type, the json value, the parent key, the parent json type, the sequence of the element at that level, the the srl no of the tag in the JSON.
create or replace PACKAGE json_traversal_update_obj_arr
AS
v_type VARCHAR2(10);
traversal_cnt NUMBER := 0;
num_json_srl_num NUMBER := 0;
new_run BOOLEAN := TRUE;
v_arr_obj_flg VARCHAR2(1) := 'N';
num_arr_obj_cnt NUMBER := 0;
TYPE object_array_key IS VARRAY(100) OF VARCHAR2(100);
v_object_array_key object_array_key := object_array_key();
TYPE obj_json_type IS VARRAY(100) OF VARCHAR2(100);
v_arr_json_typ obj_json_type := obj_json_type();
PROCEDURE PARSE_JSON(p_json_input CLOB);
PROCEDURE json_object_traversal(p_json_input CLOB);
PROCEDURE json_array_traversal (
json_document_in IN CLOB);
PROCEDURE UPDATE_JSON_VALUE(p_key VARCHAR2, p_level NUMBER, p_new_value VARCHAR2);
PROCEDURE UPDATE_JSON_NEW_VALUE(p_srl_no NUMBER, p_value VARCHAR2, p_new_value VARCHAR2);
PROCEDURE gnrt_json;
END;
create or replace PACKAGE BODY json_traversal_update_obj_arr
AS
PROCEDURE UPDATE_JSON_VALUE(p_key VARCHAR2, p_level NUMBER, p_new_value VARCHAR2)
AS
BEGIN
UPDATE JSON_MATRIX_CONFIG
SET JSON_VALUE = p_new_value
WHERE JSON_LEVEL = p_level
AND JSON_KEY = p_key;
END;
PROCEDURE UPDATE_JSON_NEW_VALUE(p_srl_no NUMBER, p_value VARCHAR2, p_new_value VARCHAR2)
AS
BEGIN
UPDATE JSON_MATRIX_CONFIG
SET JSON_VALUE = p_new_value
WHERE JSON_VALUE = p_value
AND JSON_SRL_NO = p_srl_no;
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
traversal_cnt := 0;
num_json_srl_num := 0;
v_arr_json_typ := obj_json_type();
v_object_array_key := object_array_key();
IF IS_JSON_TYPE(p_json_input) = 'object'
THEN
v_type := 'object';
json_traversal_update_obj_arr.json_object_traversal(p_json_input);
ELSIF IS_JSON_TYPE(p_json_input) = 'array'
THEN
v_type := 'array';
json_traversal_update_obj_arr.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
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
num_json_srl_num := num_json_srl_num + 1;
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);
v_json_type := IS_JSON_TYPE(value_string);
-- dbms_output.put_line('v_json_type ' || v_json_type);
CASE
WHEN
IS_JSON_TYPE(value_string) = 'object'
THEN
v_arr_json_typ.EXTEND;
v_arr_json_typ(traversal_cnt) := 'object';
v_object_array_key.EXTEND;
v_object_array_key(traversal_cnt) := key_string;
INSERT INTO JSON_MATRIX_CONFIG(json_level, json_key, json_type, level_seq, json_srl_no) VALUES (traversal_cnt, key_string, 'object', i, num_json_srl_num);
IF traversal_cnt > 1
THEN
UPDATE JSON_MATRIX_CONFIG
SET PARENT_KEY = v_object_array_key(traversal_cnt-1),
PARENT_JSON_TYPE = v_arr_json_typ(traversal_cnt-1)
WHERE JSON_LEVEL = traversal_cnt
AND PARENT_KEY IS NULL;
END IF;
JSON_OBJECT_TRAVERSAL(value_string);
WHEN IS_JSON_TYPE(value_string) = 'string'
THEN
--dbms_output.put_line('type string ' || key_string || ' ' || value_string);
IF v_arr_obj_flg = 'Y'
THEN
NULL;
-- dbms_output.put_line('arr obj true ' || num_arr_obj_cnt || ' : ' || key_string || ' ' || value_string);
END IF;
value_string := TRIM (BOTH '"' FROM (value_string));
v_object_array_key.EXTEND;
v_object_array_key(traversal_cnt) := key_string;
v_arr_json_typ.EXTEND;
v_arr_json_typ(traversal_cnt) := 'string';
IF v_arr_obj_flg = 'Y'
THEN
INSERT INTO JSON_MATRIX_CONFIG (json_level, json_key, json_type, json_value, level_seq, json_srl_no) VALUES (traversal_cnt, key_string, 'string', value_string, num_arr_obj_cnt, num_json_srl_num);
ELSE
INSERT INTO JSON_MATRIX_CONFIG (json_level, json_key, json_type, json_value, level_seq, json_srl_no) VALUES (traversal_cnt, key_string, 'string', value_string, i, num_json_srl_num);
END IF;
WHEN IS_JSON_TYPE(value_string) = 'array'
THEN
v_arr_json_typ.EXTEND;
v_arr_json_typ(traversal_cnt) := 'array';
v_object_array_key.EXTEND;
v_object_array_key(traversal_cnt) := key_string;
INSERT INTO JSON_MATRIX_CONFIG (json_level, json_key, json_type, level_seq, json_srl_no) VALUES (traversal_cnt, key_string, 'array', i, num_json_srl_num);
JSON_ARRAY_TRAVERSAL(value_string);
ELSE
DBMS_OUTPUT.put_line (
'*** No match for type on object index ' || i);
END CASE;
END LOOP;
IF traversal_cnt > 1
THEN
UPDATE JSON_MATRIX_CONFIG
SET PARENT_KEY = v_object_array_key(traversal_cnt-1),
PARENT_JSON_TYPE = v_arr_json_typ(traversal_cnt-1)
WHERE JSON_LEVEL = traversal_cnt
AND PARENT_KEY IS NULL;
END IF;
traversal_cnt := traversal_cnt - 1;
END json_object_traversal;
PROCEDURE json_array_traversal (
json_document_in IN CLOB
)
IS
l_array json_array_t;
l_array_str VARCHAR2(4000);
l_object json_object_t;
l_keys json_key_list;
l_element json_element_t;
num_arr_level_seq NUMBER := 0;
BEGIN
l_array := json_array_t.parse (json_document_in);
traversal_cnt := traversal_cnt + 1;
-- dbms_output.put_line('json_object_traversal array ' || traversal_cnt);
-- put_line ('Traverse: ' || l_array.stringify ());
FOR indx IN 0 .. l_array.get_size - 1
LOOP
num_json_srl_num := num_json_srl_num + 1;
CASE
WHEN l_array.get (indx).is_string
THEN
num_arr_level_seq := num_arr_level_seq + 1;
-- put_line('index ' || indx || ' value ' || l_array.get (indx).to_string || ' is a string');
l_array_str := l_array.get (indx).to_string;
l_array_str := TRIM (BOTH '"' FROM (l_array_str));
v_arr_json_typ.EXTEND;
v_arr_json_typ(traversal_cnt) := 'string';
INSERT INTO JSON_MATRIX_CONFIG (json_level, json_key, json_type, json_value, level_seq, json_srl_no) VALUES (traversal_cnt, null, 'string',l_array_str , num_arr_level_seq, num_json_srl_num);
WHEN l_array.get (indx).is_object
THEN
v_arr_obj_flg := 'Y';
num_arr_obj_cnt := num_arr_obj_cnt + 1;
l_object := TREAT (l_array.get (indx) AS json_object_t);
-- dbms_output.put_line(l_object.to_string || ' is an object');
traversal_cnt := traversal_cnt - 1; --decrease the JSON level as it has already been incremented
num_json_srl_num := num_json_srl_num - 1;
l_keys := l_object.get_keys;
JSON_OBJECT_TRAVERSAL(l_object.to_string);
traversal_cnt := traversal_cnt + 1; --INCREMENT the JSON level as it has already been decremented
WHEN l_array.get (indx).is_array
THEN
num_arr_level_seq := num_arr_level_seq + 1;
-- dbms_output.put_line(l_array.get (indx).stringify || ' is an array'); -- call json_array_traversal again
v_arr_json_typ.EXTEND;
v_arr_json_typ(traversal_cnt) := 'array';
INSERT INTO JSON_MATRIX_CONFIG (json_level, json_key, json_type, json_value, level_seq, json_srl_no) VALUES (traversal_cnt, null, 'array', l_array.get (indx).to_string, num_arr_level_seq, num_json_srl_num);
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;
num_arr_obj_cnt := 0;
v_arr_obj_flg := 'N';
IF traversal_cnt > 1
THEN
UPDATE JSON_MATRIX_CONFIG
SET PARENT_KEY = v_object_array_key(traversal_cnt-1),
PARENT_JSON_TYPE = v_arr_json_typ(traversal_cnt-1)
WHERE JSON_LEVEL = traversal_cnt
AND PARENT_KEY IS NULL;
END IF;
traversal_cnt := traversal_cnt - 1;
END;
PROCEDURE gnrt_json
IS
CURSOR C0
IS
SELECT JSON_KEY, JSON_SRL_NO min_srl_no,
(SELECT NVL(MIN(JSON_SRL_NO),999) FROM json_matrix_config 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 a
WHERE PARENT_KEY IS NULL
ORDER BY LEVEL_SEQ;
CURSOR C1 (min_srl_no NUMBER, max_srl_no NUMBER)
IS
SELECT DISTINCT a.JSON_LEVEL, a.PARENT_KEY, a.JSON_TYPE, b.JSON_SRL_NO
FROM json_matrix_config a, json_matrix_config b
WHERE a.JSON_SRL_NO >= min_srl_no AND a.JSON_SRL_NO < max_srl_no
AND b.JSON_KEY = a.PARENT_KEY
ORDER BY a.JSON_LEVEL DESC, b.JSON_SRL_NO;
CURSOR C2(p_parent_key VARCHAR2, p_srl_no NUMBER)
IS
SELECT PARENT_KEY, PARENT_JSON_TYPE, JSON_KEY, JSON_VALUE, JSON_TYPE, LEVEL_SEQ, JSON_SRL_NO
FROM json_matrix_config
WHERE PARENT_KEY = p_parent_key
AND JSON_SRL_NO > p_srl_no
ORDER BY JSON_SRL_NO,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_str VARCHAR2(4000);
tmp_obj_str VARCHAR2(4000);
tmp_arr_str VARCHAR2(4000);
tmp_json_key VARCHAR2(100);
prev_level_seq NUMBER := 0;
prev_parent_key VARCHAR2(100);
prev_srl_no NUMBER := 0;
key_value BOOLEAN := FALSE;
BEGIN
FOR rec_srl IN C0
LOOP --{
FOR pkey IN C1(rec_srl.min_srl_no, rec_srl.max_srl_no)
LOOP
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, pkey.JSON_SRL_NO)
LOOP
IF rec.JSON_TYPE = 'object'
THEN
IF rec.LEVEL_SEQ < prev_level_seq
THEN
CONTINUE;
END IF;
SELECT JSON_VALUE
INTO tmp_obj_str
FROM json_matrix_config
WHERE
JSON_TYPE = rec.JSON_TYPE
AND JSON_KEY = rec.JSON_KEY
AND JSON_SRL_NO = rec.JSON_SRL_NO;
-- put_line ('rec.JSON_KEY ' || rec.JSON_KEY || ' rec.JSON_TYPE ' || rec.JSON_TYPE || ' rec.JSON_SRL_NO ' || rec.JSON_SRL_NO);
-- put_line('tmp_obj_str ' || tmp_obj_str);
-- tmp_obj_str := rec.JSON_VALUE;
tmp_obj_str := TRIM (BOTH '"' FROM (tmp_obj_str));
jo_temp_1 := JSON_OBJECT_T(tmp_obj_str);
jo_temp.put(rec.JSON_KEY,jo_temp_1);
ELSIF rec.JSON_TYPE = 'array'
THEN
IF rec.LEVEL_SEQ < prev_level_seq
THEN
CONTINUE;
END IF;
SELECT JSON_VALUE
INTO tmp_arr_str
FROM json_matrix_config
WHERE
JSON_TYPE = rec.JSON_TYPE
AND JSON_KEY = rec.JSON_KEY
AND JSON_SRL_NO = rec.JSON_SRL_NO;
-- tmp_arr_str := rec.JSON_VALUE;
tmp_arr_str := TRIM (BOTH '"' FROM (tmp_arr_str));
l_arr_1 := JSON_ARRAY_T(tmp_arr_str);
jo_temp.put(rec.JSON_KEY,l_arr_1);
ELSIF rec.JSON_TYPE = 'string'
THEN
IF rec.JSON_KEY IS NULL
THEN
-- put_line ('prev_level_seq ' || prev_level_seq || ' rec.LEVEL_SEQ ' || rec.LEVEL_SEQ || ' rec.JSON_VALUE ' || rec.JSON_VALUE);
-- put_line ('rec.JSON_KEY ' || rec.JSON_KEY || ' rec.JSON_VALUE ' || rec.JSON_VALUE );
tmp_str := rec.JSON_VALUE;
tmp_str := TRIM (BOTH '"' FROM (tmp_str));
IF rec.LEVEL_SEQ <= prev_level_seq
THEN
CONTINUE;
END IF;
put_line ('tmp_str ' || tmp_str );
l_arr.append(tmp_str);
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
THEN
-- put_line ('prev_level_seq ' || prev_level_seq);
-- put_line ('jo_temp ' || jo_temp.to_string);
l_arr.append(jo_temp);
jo_temp := JSON_OBJECT_T();
END IF;
IF prev_srl_no + 1 = rec.JSON_SRL_NO AND prev_parent_key = rec.PARENT_KEY
THEN
IF rec.LEVEL_SEQ < prev_level_seq
THEN
CONTINUE;
END IF;
ELSIF rec.LEVEL_SEQ <= prev_level_seq
THEN
CONTINUE;
END IF;
tmp_str := rec.JSON_VALUE;
tmp_str := TRIM (BOTH '"' FROM (tmp_str));
jo_temp.put(rec.JSON_KEY, tmp_str);
-- put_line('jo_temp in loop' || jo_temp.to_string);
END IF;
END IF;
prev_level_seq := rec.LEVEL_SEQ;
prev_parent_key := rec.PARENT_KEY;
prev_srl_no := rec.JSON_SRL_NO;
END LOOP;
IF NOT l_arr.IS_NULL
THEN
IF key_value = TRUE
THEN
l_arr.append(jo_temp);
END IF;
tmp_arr_str := l_arr.to_string;
tmp_arr_str := TRIM (BOTH '"' FROM (tmp_arr_str));
UPDATE json_matrix_config a
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 b
WHERE JSON_KEY = pkey.PARENT_KEY
AND JSON_TYPE = 'array')
AND JSON_LEVEL = pkey.JSON_LEVEL - 1
AND JSON_SRL_NO = pkey.JSON_SRL_NO;
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 a
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 b
WHERE JSON_KEY = pkey.PARENT_KEY
AND JSON_TYPE = 'object')
AND JSON_LEVEL = pkey.JSON_LEVEL - 1
AND JSON_SRL_NO = pkey.JSON_SRL_NO;
END LOOP;
-- dbms_output.put_line('final json str ' || tmp_obj_str);
IF rec_srl.JSON_TYPE = 'string'
THEN
jo_temp_final.put(rec_srl.JSON_KEY,rec_srl.JSON_VALUE);
END IF;
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
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;
--
IF rec_srl.JSON_TYPE = 'array'
THEN
SELECT JSON_VALUE
INTO tmp_arr_str
FROM json_matrix_config
WHERE PARENT_KEY IS NULL
AND JSON_TYPE = rec_srl.JSON_TYPE
AND JSON_KEY = rec_srl.JSON_KEY;
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);
-- l_arr_final.append(l_arr_1);
END IF;
END LOOP; --} end C0
dbms_output.put_line('final obj json ' || jo_temp_final.to_string);
tmp_arr_str := TRIM (BOTH '"' FROM (jo_temp_final.to_string));
INSERT INTO JSON_MATRIX_CONFIG ( json_key, json_value) VALUES ( 'final json', tmp_arr_str);
END;
END json_traversal_update_obj_arr;
| JSON_LEVEL | JSON_KEY | JSON_TYPE | JSON_VALUE | PARENT_KEY | PARENT_JSON_TYPE | LEVEL_SEQ | JSON_SRL_NO |
|---|---|---|---|---|---|---|---|
| 1 | JsonList | object | {"JsonObj":{"objtest1":{"test1key1":"test1value1update","test1key2":"test1value2"},"ID":"1","ObjName":"jsonParserUpdate","objtest":{"key1":"value101","objtest2":{"test2key1":"test2value1","test2key2":"test2value2"},"key2":"value2"},"nestedarray":["json1","xml1"]},"keyvaluetest":"markup","projects":["jsonupdate","xml"]} | 1 | 1 | ||
| 2 | JsonObj | object | {"objtest1":{"test1key1":"test1value1update","test1key2":"test1value2"},"ID":"1","ObjName":"jsonParserUpdate","objtest":{"key1":"value101","objtest2":{"test2key1":"test2value1","test2key2":"test2value2"},"key2":"value2"},"nestedarray":["json1","xml1"]} | JsonList | object | 1 | 2 |
| 3 | objtest1 | object | {"test1key1":"test1value1update","test1key2":"test1value2"} | JsonObj | object | 1 | 3 |
| 4 | test1key1 | string | test1value1update | objtest1 | object | 1 | 4 |
| 4 | test1key2 | string | test1value2 | objtest1 | object | 2 | 5 |
| 3 | ID | string | 1 | JsonObj | object | 2 | 6 |
| 3 | ObjName | string | jsonParserUpdate | JsonObj | object | 3 | 7 |
| 3 | objtest | object | {"key1":"value101","objtest2":{"test2key1":"test2value1","test2key2":"test2value2"},"key2":"value2"} | JsonObj | object | 4 | 8 |
| 4 | key1 | string | value101 | 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 | ["jsonupdate","xml"] | JsonList | object | 3 | 18 |
| 3 | string | jsonupdate | projects | array | 1 | 19 | |
| 3 | string | xml | projects | array | 2 | 20 | |
| 1 | employees | array | [{"name":"Shelley,Higgins","job":"Accounting Manager"},{"name":"William,Gietz,Sr","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,Sr | employees | array | 2 | 24 |
| 2 | job | string | Public Accountant | employees | array | 2 | 25 |
| final json | {"JsonList":{"JsonObj":{"objtest1":{"test1key1":"test1value1update","test1key2":"test1value2"},"ID":"1","ObjName":"jsonParserUpdate","objtest":{"key1":"value101","objtest2":{"test2key1":"test2value1","test2key2":"test2value2"},"key2":"value2"},"nestedarray":["json1","xml1"]},"keyvaluetest":"markup","projects":["jsonupdate","xml"]},"employees":[{"name":"Shelley,Higgins","job":"Accounting Manager"},{"name":"William,Gietz,Sr","job":"Public Accountant"}]} |
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"
},
"nestedarray": [
"json1",
"xml1"
]
},
"keyvaluetest": "markup",
"projects": [
"json",
"xml"
]
},
"employees": [
{
"name": "Shelley,Higgins",
"job": "Accounting Manager"
},
{
"name": "William,Gietz",
"job": "Public Accountant"
}
]
}';
EXECUTE IMMEDIATE 'TRUNCATE TABLE JSON_MATRIX_CONFIG';
JSON_TRAVERSAL_UPDATE_OBJ_ARR.PARSE_JSON(
P_JSON_INPUT => P_JSON_INPUT
);
JSON_TRAVERSAL_UPDATE_OBJ_ARR.UPDATE_JSON_NEW_VALUE(7, 'jsonParser', 'jsonParserUpdate');
JSON_TRAVERSAL_UPDATE_OBJ_ARR.UPDATE_JSON_NEW_VALUE(9, 'value1', 'value101');
JSON_TRAVERSAL_UPDATE_OBJ_ARR.UPDATE_JSON_NEW_VALUE(17, 'markupup', 'markupupdate');
JSON_TRAVERSAL_UPDATE_OBJ_ARR.UPDATE_JSON_NEW_VALUE(4, 'test1value1', 'test1value1update');
JSON_TRAVERSAL_UPDATE_OBJ_ARR.UPDATE_JSON_NEW_VALUE(19, 'json', 'jsonupdate');
JSON_TRAVERSAL_UPDATE_OBJ_ARR.UPDATE_JSON_NEW_VALUE(24, 'William,Gietz', 'William,Gietz,Sr');
JSON_TRAVERSAL_UPDATE_OBJ_ARR.gnrt_json;
--rollback;
END;
The new JSON with the updated values
{
"JsonList": {
"JsonObj": {
"objtest1": {
"test1key1": "test1value1update",
"test1key2": "test1value2"
},
"ID": "1",
"ObjName": "jsonParserUpdate",
"objtest": {
"key1": "value101",
"objtest2": {
"test2key1": "test2value1",
"test2key2": "test2value2"
},
"key2": "value2"
},
"nestedarray": [
"json1",
"xml1"
]
},
"keyvaluetest": "markup",
"projects": [
"jsonupdate",
"xml"
]
},
"employees": [
{
"name": "Shelley,Higgins",
"job": "Accounting Manager"
},
{
"name": "William,Gietz,Sr",
"job": "Public Accountant"
}
]
}Thus we see that the parser has worked for a JSON having multiple levels of nesting, objects and arrays.
For this parser to work for JSONs of all types, shapes and sizes some tweaking of the code will need to be done, to make it work for different scenarios.
There are few bugs in this code which need to be ironed out to make it a more robust parser for most json types.
For Example, I try it our for 2 random JSONs. It has worked fine for one JSON and slight mismatch in values for the second, though the JSON structure has been retained.
For now I leave these bugs in the parser/generator as is, for the reader of this post to tweak and fix. You can give me the fix as a comment, and I will be glad incorporate it in the code.
I try the same with another sample JSON taken from
https://support.oneskyapp.com/hc/en-us/articles/208047697-JSON-sample-files link
-- and the JSON parsing and JSON generation logic have worked fine.
DECLARE
P_JSON_INPUT CLOB;
BEGIN
P_JSON_INPUT := '{
"quiz": {
"sport": {
"q1": {
"question": "Which one is correct team name in NBA?",
"options": [
"New York Bulls",
"Los Angeles Kings",
"Golden State Warriros",
"Huston Rocket"
],
"answer": "Huston Rocket"
}
},
"maths": {
"q1": {
"question": "5 + 7 = ?",
"options": [
"10",
"11",
"12",
"13"
],
"answer": "12"
},
"q2": {
"question": "12 - 8 = ?",
"options": [
"1",
"2",
"3",
"4"
],
"answer": "4"
}
}
}
}';
EXECUTE IMMEDIATE 'TRUNCATE TABLE JSON_MATRIX_CONFIG';
JSON_TRAVERSAL_UPDATE_OBJ_ARR.PARSE_JSON(
P_JSON_INPUT => P_JSON_INPUT
);
JSON_TRAVERSAL_UPDATE_OBJ_ARR.gnrt_json;
--rollback;
END;
{
"quiz": {
"sport": {
"q1": {
"question": "Which one is correct team name in NBA?",
"options": [
"New York Bulls",
"Los Angeles Kings",
"Golden State Warriros",
"Huston Rocket"
],
"answer": "Huston Rocket"
}
},
"maths": {
"q1": {
"question": "5 + 7 = ?",
"options": [
"10",
"11",
"12",
"13"
],
"answer": "12"
},
"q2": {
"question": "12 - 8 = ?",
"options": [
"1",
"2",
"3",
"4"
],
"answer": "4"
}
}
}
}
Below JSON which is getting parsed, but there is a mismatch in the output and input JSON for the JSON which has got recreated.
The JSON_TRAVERSAL_UPDATE_OBJ_ARR.gnrt_json has to be revisited. The cursors which are selecting the data at each level for recreating the JSON have to be re written to select the correct data for each JSON sub object being generated. Currently these queries are selecting more data for each loop of parsing, and so a lot of IFs and ELSEs have been put in the code to skip irrelevant data for each set of data being processsed. This is making the program too logikesh.
Adding more logic to the code is will only add to the complexity and will impact the JSON generation logic for existing JSONs which are working.For instance, the LineItems array of objects is fetching the data for both the objects in the array for each iteration. The cursor query needs to be modified to handle such a scenarion.
Secondly there is a bug in the traversal logic in the case of an array of objects containing objects and tag-value pairs - LineItems - where in the LEVEL_SEQ column is not getting properly populated for such types of array-object combinations, resulting in errors in the final output.
Once these issues are ironed out the JSON generator code will be cleaner and will work for a wider range of JSON types and sizes with various levels of nesting.
DECLARE
P_JSON_INPUT CLOB;
BEGIN
P_JSON_INPUT := '{
"Reference": "ABULL-20140421",
"Requestor": "Alexis Bull",
"User": "ABULL",
"CostCenter": "A50",
"ShippingInstructions": {
"name": "Alexis Bull",
"Address": {
"street": "200 Sporting Green",
"city": "South San Francisco",
"state": "CA",
"zipCode": 99236,
"country": "United States of America"
},
"Phone": [
{
"type": "Office",
"number": "909-555-7307"
},
{
"type": "Mobile",
"number": "415-555-1234"
}
]
},
"Special Instructions": null,
"AllowPartialShipment": true,
"LineItems": [
{
"ItemNumber": 1,
"Part": {
"Description": "One Magic Christmas",
"UnitPrice": 19.95,
"UPCCode": 13131092899
},
"Quantity": 9
},
{
"ItemNumber": 2,
"Part": {
"Description": "Lethal Weapon",
"UnitPrice": 19.95,
"UPCCode": 85391628927
},
"Quantity": 5
}
],
"PONumber": 1700,
"totalQuantity": 14,
"totalPrice": 279.3
}';
EXECUTE IMMEDIATE 'TRUNCATE TABLE JSON_MATRIX_CONFIG';
JSON_TRAVERSAL_UPDATE_OBJ_ARR.PARSE_JSON(
P_JSON_INPUT => P_JSON_INPUT
);
JSON_TRAVERSAL_UPDATE_OBJ_ARR.gnrt_json;
--rollback;
END;
{
"Reference": "ABULL-20140421",
"Requestor": "Alexis Bull",
"User": "ABULL",
"CostCenter": "A50",
"ShippingInstructions": {
"name": "Alexis Bull",
"Address": {
"street": "200 Sporting Green",
"city": "South San Francisco",
"state": "CA",
"zipCode": "99236",
"country": "United States of America"
},
"Phone": [
{
"type": "Office",
"number": "909-555-7307"
},
{
"type": "Mobile",
"number": "415-555-1234"
}
]
},
"Special Instructions": "null",
"AllowPartialShipment": "true",
"LineItems": [
{
"ItemNumber": "1",
"Part": {
"Description": "Lethal Weapon",
"UnitPrice": "19.95",
"UPCCode": "85391628927"
}
},
{
"Part": {
"Description": "Lethal Weapon",
"UnitPrice": "19.95",
"UPCCode": "85391628927"
}
}
],
"PONumber": "1700",
"totalQuantity": "14",
"totalPrice": "279.3"
}
Unless the LORD builds the house,
those who build it labor in vain.
Unless the LORD guards the city,
the guards keep watch in vain.
It is in vain you rise up early,
and go late to rest,
Eating the bread of anxious toil;
for he gives sleep to His beloved.
Psalm 127:1-2
No comments:
Post a Comment