Create the sample JSON objects and data
CREATE TABLE j_purchaseorder
(id VARCHAR2 (32) NOT NULL PRIMARY KEY,
date_loaded TIMESTAMP (6) WITH TIME ZONE,
po_document VARCHAR2 (4000)
CONSTRAINT ensure_json CHECK (po_document is json));
INSERT INTO j_purchaseorder
VALUES (
SYS_GUID(),
to_date('30-DEC-2014'),
'{"PONumber" : 1600,
"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.0},
{"ItemNumber" : 2,
"Part" : {"Description" : "Lethal Weapon",
"UnitPrice" : 19.95,
"UPCCode" : 85391628927},
"Quantity" : 5.0}]}');
INSERT INTO j_purchaseorder
VALUES (
SYS_GUID(),
to_date('31-DEC-2015'),
'{"PONumber" : 2600,
"Reference" : "SBELL-20141017",
"Requestor" : "Sarah Bell",
"User" : "SBELL",
"CostCenter" : "A50",
"ShippingInstructions" : {"name" : "Sarah Bell",
"Address" : {"street" : "200 Sporting Green",
"city" : "South San Francisco",
"state" : "CA",
"zipCode" : 99236,
"country" : "United States of America"},
"Phone" : "983-555-6509"},
"Special Instructions" : "Courier",
"LineItems" :
[{"ItemNumber" : 1,
"Part" : {"Description" : "Making the Grade",
"UnitPrice" : 20,
"UPCCode" : 27616867759},
"Quantity" : 8.0},
{"ItemNumber" : 2,
"Part" : {"Description" : "Nixon",
"UnitPrice" : 19.95,
"UPCCode" : 717951002396},
"Quantity" : 5},
{"ItemNumber" : 3,
"Part" : {"Description" : "Eric Clapton: Best Of 1981-1999",
"UnitPrice" : 19.95,
"UPCCode" : 75993851120},
"Quantity" : 5.0}]}');
In this function I am modifying the PO Number and adding up the total order quantity and price.
Sample JSON
CREATE OR REPLACE FUNCTION modify_order(purchaseOrder IN VARCHAR2) RETURN VARCHAR2 IS
po_obj JSON_OBJECT_T;
li_arr JSON_ARRAY_T;
li_item JSON_ELEMENT_T;
li_obj JSON_OBJECT_T;
unitPrice NUMBER;
quantity NUMBER;
ponumber NUMBER;
totalPrice NUMBER := 0;
totalQuantity NUMBER := 0;
BEGIN
po_obj := JSON_OBJECT_T.parse(purchaseOrder);
li_arr := po_obj.get_Array('LineItems');
ponumber := po_obj.get_String('PONumber');
ponumber := ponumber + 100;
po_obj.put('PONumber', ponumber);
FOR i IN 0 .. li_arr.get_size - 1 LOOP
li_obj := JSON_OBJECT_T(li_arr.get(i));
quantity := li_obj.get_Number('Quantity');
unitPrice := li_obj.get_Object('Part').get_Number('UnitPrice');
totalPrice := totalPrice + (quantity * unitPrice);
totalQuantity := totalQuantity + quantity;
END LOOP;
po_obj.put('totalQuantity', totalQuantity);
po_obj.put('totalPrice', totalPrice);
RETURN po_obj.to_string;
END;
UPDATE j_purchaseorder SET (po_document) = modify_order(po_document);
SELECT po.po_document.PONumber FROM j_purchaseorder po WHERE po.po_document.PONumber = 1700;
{
"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
}
This is changing the JSON structure. We need to find a method to update the JSON without changing the order of the JSON attributes.
If there are built in JSON methods in PLSQL, to modify a JSON value without altering the structure that still I need to check. An alternate approach would be to rebuild the JSON and keep adding to the new JSON the updated values. This will make the program a little more complex, but it will do as a work around.
In the below example I am having the Employee Department object, with an array of employees in that department.
{
"department": "Accounting",
"employees": [
{
"name": "Shelley,Higgins",
"job": "Accounting Manager"
},
{
"name": "William,Gietz",
"job": "Public Accountant"
}
]
}
Below is the code for the same.
References
DECLARE
jo JSON_OBJECT_T;
jo_1 JSON_OBJECT_T;
new_json CLOB;
keys JSON_KEY_LIST;
emp_keys JSON_KEY_LIST;
elem_1 VARCHAR2(100);
key_string VARCHAR2(1000);
value_string VARCHAR2(1000);
emp_key_string VARCHAR2(1000);
emp_value_string VARCHAR2(1000);
l_ja JSON_ARRAY_T;
l_ja_1 JSON_ARRAY_T;
l_emp_obj JSON_OBJECT_T; -- For handling individual employee objects
l_emp_obj_1 JSON_OBJECT_T;
BEGIN
-- Parse JSON object
jo := JSON_OBJECT_T.parse('{
"department": "Accounting",
"employees": [
{
"name": "Shelley,Higgins",
"job": "Accounting Manager"
},
{
"name": "William,Gietz",
"job": "Public Accountant"
}
]
}');
jo_1 := JSON_OBJECT_T(); --initialize the new department JSON object
-- 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;
DBMS_OUTPUT.put_line('Key: ' || key_string);
DBMS_OUTPUT.put_line('Value: ' || value_string);
IF value_string = '"Accounting"'
THEN
value_string := 'Accounting and Finance'; -- Change the department name to Accounting and Finance
END IF;
dbms_output.put_line('value_string ' || value_string);
IF key_string != 'employees' THEN
jo_1.put(key_string, value_string); -- In a loop add the key value pair to the new JSON object jo_1
-- Check if the key is 'employees'
ELSIF key_string = 'employees' THEN -- Check in the JSON object for the employees array
DBMS_OUTPUT.put_line('Employees JSON array: ' || value_string);
-- Parse the 'employees' array
l_ja := JSON_ARRAY_T.parse(value_string);
-- Loop through each employee object in the array
l_ja_1 := JSON_ARRAY_T(); --initialize the new employees JSON array
FOR j IN 0..l_ja.get_size - 1 LOOP
-- Get the individual employee object
l_emp_obj := JSON_OBJECT_T(l_ja.get(j)); --assign each employee data in the array to the employee object.
DBMS_OUTPUT.put_line('Employee object ' || j || ' ' || l_ja.get(j).to_string);
-- Retrieve the keys for the employee object (name, job)
emp_keys := l_emp_obj.get_keys;
-- Loop through the employee keys (name, job)
l_emp_obj_1 := JSON_OBJECT_T(); --Initialize the temporary employee object
FOR k IN 1..emp_keys.COUNT LOOP
emp_key_string := emp_keys(k);
-- DBMS_OUTPUT.put_line('key string ' || k || ' ' || emp_key_string );
emp_value_string := l_emp_obj.get(emp_key_string).to_string;
IF emp_key_string = 'job' AND emp_value_string = '"Accounting Manager"' -- build the logic here to modify the employee object data
THEN
emp_value_string := 'Accounting and Finance Manager'; -- change job profile to Accounting and Finance Manager
END IF;
emp_value_string := TRIM (BOTH '"' FROM (emp_value_string)); --trim the "" from the value element beofre assigning to the temporary employee object
l_emp_obj_1.put(emp_key_string, emp_value_string); --assign the modified key value pair to the temporary employee object
-- Output employee key-value pairs
-- DBMS_OUTPUT.put_line('Employee ' || j || ' ' || emp_key_string || ': ' || emp_value_string);
END LOOP;
l_ja_1.append(l_emp_obj_1); -- append the temporary employee object to the new employees JSON array
-- DBMS_OUTPUT.put_line('Employee object 1 ' || l_emp_obj_1.stringify);
END LOOP;
jo_1.put(key_string, l_ja_1); -- once the new employees JSON array is build assign it to the new department JSON object
END IF;
END LOOP;
new_json := jo_1.stringify;
dbms_output.put_line('json obj' || new_json); --this will show the modified department JSON with the structure maintained as is.
END;
/
{
"department": "Accounting and Finance",
"employees": [
{
"name": "Shelley,Higgins",
"job": "Accounting and Finance Manager"
},
{
"name": "William,Gietz",
"job": "Public Accountant"
}
]
}
This is not a generic parser and it has certain limitations, like it assumes that the JSON will be an object.
If the input JSON is an array this block will fail. To make the parser more generic we can put a check in the beginning for the object like if it is a JSON object or a JSON array.
We will first understand need to understand some of the common JSON methods and object types.
To be continued ...
Thought for the day
Do not grow weary when you pray;
do not neglect to give alms
Do not ridicule a person who is embittered in spirit,
for there is One who humbles and exalts
Do not devise a lie against your brother
or do the same to a friend.
Refuse to utter any lie,
for it is a habit that results in no good.
Sirach 7:10-13
No comments:
Post a Comment