In many applications data is stored in the database as a JSON objects. Based on the nature of the data and usage, modifications on this data become imperative. For this we require to do updates on the database as per the requirements. For this we can use common JSON methods like JSON_MERGEPATCH and JSON_TRANSFORM. But if there are some complex computations, or some interdependecies between the elements of the JSON, using the methods to update the JSONs will have its limitations.
In such cases, the best option would be to traverse through each element of the JSON object or JSON array and make the required changes to the JSON. We can then do a single update to the database and store the modified JSON.
In my previous post A generic JSON parser with Oracle PL/SQL
I have shown how to traverse through a JSON and access each element.
We can enhance the same JSON parsing and traversal logic to make modifications to the individual JSON elements. For this we make use of the put METHOD of JSON_OBJECT and append METHOD of JSON_ARRAY types. The limitation of these methods is that the modified element will get added to the end of the JSON. To work around this limitation we need to recreate the JSON and add the modified elements to the JSON in the same order as the original JSON.
When traversing the JSON, we first identify if the JSON is an object or an array. Once this is identified we can the procedure to accordingly traverse through the JSON.
If the JSON is an object we call the json_object_traversal method to traverse through and update the JSON object.
If the JSON is an array we call the json_array_traversal method to traverse through and update the JSON array.
The logic for traversing though the object or array is already explained in my previous post. I use the same logic again with enhancements to create a new object or array with the modified data.
This parser will work for certain combinations of JSON arrays and objects like the ones given in the example below. JSONs can combine in a variety of combinations of arrays and objects. Each JSON type has to be taken on a case to case basis to provide a parser which will update for all types and combinations of JSONs. The below package can provide a basis for building and enhancing the functionality for the same.
Below is the code for the same.
CREATE OR REPLACE PACKAGE json_traversal_update AS
jo_1 JSON_OBJECT_T;
jo_temp JSON_OBJECT_T;
ja_1 JSON_ARRAY_T;
ja_temp JSON_ARRAY_T;
bl_array_obj BOOLEAN := FALSE;
bl_tmp_array BOOLEAN := FALSE;
g_key_string VARCHAR2(100);
v_type VARCHAR2(10);
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_update
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
v_type := 'object';
jo_1 := JSON_OBJECT_T(); --initialize the new JSON object
json_traversal_update.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.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);
jo_temp := JSON_OBJECT_T(); --initialize the temp JSON object
-- Retrieve keys of the main JSON object
keys := jo.get_keys;
-- Loop through the main keys (department, employees)
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;
IF value_string = '"Accounting"' --For each JSON object key-valuue pair, make changes based on modified logic
THEN
value_string := 'Accounting and Finance'; -- Change the department name to Accounting and Finance
END IF;
IF key_string = 'job' AND value_string = '"Accounting Manager"' -- build the logic here to modify the employee object data
THEN
value_string := 'Accounting and Finance Manager'; -- change job profile to Accounting and Finance Manager
END IF;
value_string := TRIM (BOTH '"' FROM (value_string)); --trim the "" from the value element beofre assigning to the temporary employee object
IF IS_JSON_TYPE(value_string) = 'array'
THEN
g_key_string := key_string;
json_array_traversal(value_string);
ELSE
DBMS_OUTPUT.put_line('Key: ' || key_string);
DBMS_OUTPUT.put_line('Value: ' || value_string);
IF bl_array_obj = TRUE
THEN
put_line('true');
jo_temp.put(key_string, value_string); -- In a loop add the key value pair to the temporary object
ELSE
put_line('false');
jo_1.put(key_string, value_string); --assign the modified key value pair to the new JSON object jo_1
END IF;
END IF;
END LOOP;
IF v_type = 'object' THEN
dbms_output.put_line('New object ' || jo_1.to_string);
END IF;
END json_object_traversal;
--level_in IN INTEGER DEFAULT 0
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;
l_temp_string VARCHAR2(100);
BEGIN
l_array := json_array_t.parse (json_document_in);
-- put_line ('Traverse: ' || l_array.stringify ());
ja_temp := JSON_ARRAY_T(); --initialize the new JSON array
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
-- bl_array_obj := FALSE;
put_line(l_array.get (indx).to_string || ' is a string');
l_temp_string := l_array.get (indx).to_string;
IF l_temp_string = '"Alphonso"'
THEN
l_temp_string := 'Ratnagiri Alphonso';
END IF;
l_temp_string := TRIM (BOTH '"' FROM (l_temp_string));
IF bl_tmp_array = TRUE
THEN
ja_temp.append(l_temp_string);
ELSE
ja_1.append(l_temp_string);
END IF;
WHEN l_array.get (indx).is_object
THEN
bl_array_obj := TRUE;
-- jo_temp := JSON_OBJECT_T(l_array.get(indx)); --assign each employee data in the array to the employee object.
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);
dbms_output.put_line(jo_temp.to_string || ' after object traversal');
dbms_output.put_line(ja_temp.to_string || ' after temp array put');
IF v_type = 'array'
THEN
put_line('array check 1 ' || ja_temp.stringify());
ja_1.append(jo_temp);
bl_array_obj := FALSE;
ELSE
ja_temp.append(jo_temp); -- append the temporary object to the new JSON array
END IF;
-- bl_array_obj := FALSE;
WHEN l_array.get (indx).is_array
THEN
-- bl_array_obj := FALSE;
bl_tmp_array := TRUE;
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;
IF bl_array_obj = TRUE
THEN
IF v_type = 'object'
THEN
jo_1.put(g_key_string, ja_temp);
bl_array_obj := FALSE;
-- ELSIF v_type = 'array'
-- THEN
-- put_line('array check ' || ja_temp.stringify());
-- ja_1.append(ja_temp);
END IF;
END IF;
IF bl_tmp_array = TRUE
THEN
IF v_type = 'array'
THEN
ja_1.append(ja_temp);
bl_tmp_array := FALSE;
END IF;
END IF;
IF v_type = 'array' THEN
dbms_output.put_line ('New array ' || ja_1.stringify ());
END IF;
END;
END json_traversal_update;
/
DECLARE
P_JSON_INPUT CLOB;
BEGIN
P_JSON_INPUT := '["Fruits",
{"name":"Apple"},
"Mangoes",
["Alphonso","Devgadh","Dussehri"]
]';
json_traversal_update.PARSE_JSON(
P_JSON_INPUT => P_JSON_INPUT
);
--rollback;
END;
[
"Fruits",
{
"name": "Apple"
},
"Mangoes",
[
"Ratnagiri Alphonso",
"Devgadh",
"Dussehri"
]
]
DECLARE
P_JSON_INPUT CLOB;
BEGIN
P_JSON_INPUT := '{
"department": "Accounting",
"employees": [
{
"name": "Shelley,Higgins",
"job": "Accounting Manager"
},
{
"name": "William,Gietz",
"job": "Public Accountant"
}
]
}';
json_traversal_update.PARSE_JSON(
P_JSON_INPUT => P_JSON_INPUT
);
--rollback;
END;
{
"department": "Accounting and Finance",
"employees": [
{
"name": "Shelley,Higgins",
"job": "Accounting and Finance Manager"
},
{
"name": "William,Gietz",
"job": "Public Accountant"
}
]
}
No comments:
Post a Comment