JSON (JavaScript Object Notation) is a text-based format for storing and exchanging data across applications and networks in a way that is human readable and machine parsable. This makes JSON formats relatively easy to learn and troubleshoot. Because of its simplicity and easy readibility, it has grown into a very capable data format that simplifies data interchange across diverse platforms and programming languages.
JSON supports several datatypes, some of the main types include
Below are some of the key uses of these JSON data types.
JSON_ELEMENT_T : It is a supertype that some of the other object types extend. Normally we dont use this datatype directly. You can cast it as a subtype like a JSON OBJECT using TREAT AS
Example
l_obj := TREAT (l_elem AS JSON_OBJECT_T);
v_color := JSON_ELEMENT_T.parse('{"color": "yellow"}');
Adding a key-value pair to an element type
DECLARE
v_color JSON_ELEMENT_T;
BEGIN
v_color := JSON_ELEMENT_T.parse('{"color": "yellow"}');
pkg_display.display(v_color);
END;
{"color":"yellow"}
DECLARE
v_color JSON_ELEMENT_T;
l_arr JSON_ARRAY_T;
BEGIN
l_arr := JSON_ARRAY_T(); --initialize the array
v_color := JSON_ELEMENT_T.parse('{"color": "yellow"}');
l_arr := JSON_ARRAY_T('[""]'); --assign an empty place holder to the array, or multiple comma separated "" place holders
l_arr.put(0, JSON_ELEMENT_T.parse('{"color":"green"}'), TRUE);
pkg_display.display(l_arr);
l_arr.append(v_color);
pkg_display.display(l_arr);
--replace yellow with blue. At position 1 already there is an element.
l_arr.put(1, JSON_ELEMENT_T.parse('{"color":"blue"}'), TRUE);
pkg_display.display(l_arr);
END;
[{"color":"green"}]
[{"color":"green"},{"color":"yellow"}]
[{"color":"green"},{"color":"blue"}]
To check if the JSON is an array
DECLARE
l_str CLOB := '["grape","orange","mango","banana"]';
l_arr JSON_ARRAY_T;
BEGIN
l_arr := JSON_ARRAY_T(l_str); --initialize the array
IF l_arr.IS_ARRAY
THEN
dbms_output.put_line('is array');
ELSE
dbms_output.put_line('is not an array');
END IF;
END;
To check if the JSON is an object
DECLARE
l_str CLOB := '{"employee_id" : 100, "employee_name" : "Stephen King"}';
l_obj JSON_OBJECT_T;
BEGIN
l_obj := JSON_OBJECT_T(l_str); --initialize the array
IF l_obj.IS_OBJECT
THEN
dbms_output.put_line('is object');
ELSE
dbms_output.put_line('is not an object');
END IF;
END;
/
GET_OBJECT method in JSON
GET_OBJECT returns the object associated with a specific key from a JSON.
If it is a nested JSON, with multiple layers, you have to traverse through the hierarchy of each object layer.
GET_STRING returns the string associated with a specific key value pair in a JSON object.
Below is an example
DECLARE
v_json_sub_obj JSON_OBJECT_T := JSON_OBJECT_T();
v_json_clob CLOB := '{
"JsonList": {
"JsonObj": {
"objtest1": {
"test1key1": "test1value1",
"test1key2": "test1value2"
},
"ID": "1",
"ObjName": "jsonParser",
"objtest": {
"key1": "value1",
"objtest2": {
"test2key1": "test2value1",
"test2key2": "test2value2"
},
"key2": "value2"
}
},
"keyvaluetest": "markup"
}
}';
v_json_obj JSON_OBJECT_T;
BEGIN
v_json_obj := JSON_OBJECT_T.parse(v_json_clob);
v_json_sub_obj := v_json_obj.get_object('JsonList').get_object('JsonObj');
-- pkg_display.display(l_arr);
dbms_output.put_line(v_json_sub_obj.get_object('objtest1').get_string('test1key1')); -- print the value under JsonList.JsonObj.objtest1.test1key1
dbms_output.put_line(v_json_sub_obj.get_object('objtest').get_object('objtest2').get_string('test2key1')); -- print the value under JsonList.JsonObj.objtest.objtest2.test2key1
dbms_output.put_line(v_json_sub_obj.to_string); -- print the object under JsonList.JsonObj
dbms_output.put_line(v_json_sub_obj.get_string('ID')); --print the value under JsonList.JsonObj.ID
-- dbms_output.put_line(v_json_sub_obj.get_string('test2key1'));
--get_object('objtest2').
END;
value for key test1key1 : test1value1
value for key test2key1 : test2value1
value for object JsonObj : {
"objtest1": {
"test1key1": "test1value1",
"test1key2": "test1value2"
},
"ID": "1",
"ObjName": "jsonParser",
"objtest": {
"key1": "value1",
"objtest2": {
"test2key1": "test2value1",
"test2key2": "test2value2"
},
"key2": "value2"
}
}
value for key ID : 1
GET_ARRAY method in JSON
Returns the JSON_ARRAY_T associated with the specified key. If you want to extract a particular JSON array element from a JSON, we use the GET_ARRAY method.
Example :- To extract the LineItems array from the purchase order JSON below is the code.
DECLARE
purchaseOrder VARCHAR2(4000) := '{"PONumber": 1600,
"Requestor": "Alexis Bull",
"CostCenter": "A50",
"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}],
"totalQuantity": 14,
"totalPrice": 279.3}';
jo JSON_OBJECT_T;
ja JSON_ARRAY_T;
keys_string VARCHAR2(1000);
BEGIN
ja := new JSON_ARRAY_T;
jo := JSON_OBJECT_T.parse(purchaseOrder);
ja := jo.get_array('LineItems');
keys_string := ja.to_string;
DBMS_OUTPUT.put_line(keys_string);
END;
/
[
{
"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
}
]
GET_DATE method in JSON_OBJECT_T
This method by default expects the object value to be in YYYY-MM-DD format.
For any other format it will give an error
ORA-40566: JSON path expression selected a value of different data type.
declare
jo JSON_OBJECT_T;
begin
jo := JSON_OBJECT_T.parse('{"dob":"2025-07-06"}');
dbms_output.put_line(jo.GET_Date('dob'));
end;
/
06-JUL-2025 00:00:00Code to display the JSON data.
CREATE OR REPLACE PACKAGE PKG_DISPLAY
AS
PROCEDURE display (p_obj IN JSON_OBJECT_T);
PROCEDURE display (p_arr IN JSON_ARRAY_T);
PROCEDURE display (p_elem IN JSON_ELEMENT_T);
END;
/
CREATE OR REPLACE PACKAGE BODY PKG_DISPLAY
AS
PROCEDURE display (p_obj IN JSON_OBJECT_T) IS
BEGIN
DBMS_OUTPUT.put_line(p_obj.stringify);
END;
PROCEDURE display (p_arr IN JSON_ARRAY_T) IS
BEGIN
DBMS_OUTPUT.put_line(p_arr.stringify);
END;
PROCEDURE display (p_elem IN JSON_ELEMENT_T) IS
BEGIN
DBMS_OUTPUT.put_line(p_elem.stringify);
END;
END;
/
JSON Data Structures
God's Word for the day
Do not make fun of one who is ill-bred
or your ancestors may be insulted.
Do not reproach one who is turning away from sin;
remember that we all deserve punishment.
Do not disdain one who is old,
for some of us are also growing old
Do not rejoice over anyone's death
remember that we must all die.
Sirach 8:4-7
No comments:
Post a Comment