Reading a simple JSON with PLSQL using TYPES json_array_t and json_object_t
DECLARE
p_in VARCHAR2(4000) := '[{"student_roll_no":10004},{"student_roll_no":10005}]';
l_ja json_array_t;
l_jo json_object_t;
BEGIN
l_ja := json_array_t.parse(p_in);
for i in 0..l_ja.get_size - 1 loop
l_jo := json_object_t(l_ja.get(i));
dbms_output.put_line('l_jo ' || l_jo.to_string);
end loop;
END;
/
l_jo {"student_roll_no":10004}
l_jo {"student_roll_no":10005}
For this I try using the JSON_OBJECT get_keys functions.
Example for same is below
DECLARE
jo JSON_OBJECT_T;
ja JSON_ARRAY_T;
keys JSON_KEY_LIST;
keys_string VARCHAR2(100);
BEGIN
ja := new JSON_ARRAY_T;
jo := JSON_OBJECT_T.parse('{"name":"Beda",
"jobTitle":"codmonki",
"projects":["json", "xml"]}');
keys := jo.get_keys;
FOR i IN 1..keys.COUNT LOOP
ja.append(keys(i));
END LOOP;
keys_string := ja.to_string;
DBMS_OUTPUT.put_line(keys_string);
END;
/
["name","jobTitle","projects"]
declare
jdoc varchar2(1000) := '{
"department": "Accounting",
"employees": [
{
"name": "Shelley,Higgins",
"job": "Accounting Manager"
},
{
"name": "William,Gietz",
"job": "Public Accountant"
}
]
}';
jobj json_object_t;
keys json_key_list;
jarr json_array_t;
arrkeys json_key_list;
arrvals VARCHAR2(1000);
elem json_element_t;
value_string VARCHAR2(1000);
begin
jobj := json_object_t ( jdoc );
keys := jobj.get_keys;
for i in 1 .. keys.count loop
dbms_output.put_line(keys(i));
if jobj.get(keys(i)).is_array then
jarr := jobj.get_array(keys(i));
elem := jarr.get(1);
arrkeys := treat ( elem as json_object_t ).get_keys;
for j in 1 .. arrkeys.count loop
dbms_output.put_line(' ' || arrkeys(j));
end loop;
end if;
end loop;
end;
/
department
employees
name
job
This still gives me only the keys for the JSON.
Our objective is to get keys in one array and the corresponding values in another array.
I try 1 more PLSQL bock as below
DECLARE
jo JSON_OBJECT_T;
ja JSON_ARRAY_T;
jv JSON_ARRAY_T; -- Initialize the array for values
keys JSON_KEY_LIST;
keys_string VARCHAR2(100);
key_string VARCHAR2(1000);
value_string VARCHAR2(1000);
v_project VARCHAR2(1000);
l_ja json_array_t;
l_po json_object_t;
BEGIN
-- Initialize arrays
ja := new JSON_ARRAY_T;
jv := new JSON_ARRAY_T; -- Initialize jv as well
-- Parse JSON object
jo := JSON_OBJECT_T.parse('{
"name":"Beda",
"jobTitle":"codmonki",
"projects":["json", "xml"]
}');
-- Retrieve keys
keys := jo.get_keys;
-- Loop through the keys and retrieve corresponding values
FOR i IN 1..keys.COUNT LOOP
key_string := keys(i);
-- Retrieve the value using the get method 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 key_string = 'projects' THEN
l_ja := json_array_t.parse(value_string);
FOR j IN 0 .. l_ja.get_size - 1 LOOP
v_project := l_ja.get(j).to_string;
DBMS_OUTPUT.put_line('Project: ' || v_project); -- Output each project value
END LOOP;
END IF;
END LOOP;
END;
/
Key: name
Value: "Beda"
Key: jobTitle
Value: "codmonki"
Key: projects
Value: ["json","xml"]
Project: "json"
Project: "xml"
From the example PLSQL blocks above, we can see that parsing a JSON involves 2 steps
1. Parsing the JSON object.
2. Parsing the JSON array if any
Parsing the JSON object.
We first parse the JSON object using JSON_OBJECT_T.parse and assign it to JSON_OBJECT_T type variable jo.
We get the keys of the JSON object using get_keys method of JSON_OBJECT_T.
To get the value of each key element in the object, in a FOR loop with index i, we call
jo.get(keys(i)).to_string
jo := JSON_OBJECT_T.parse('json_object');
keys := jo.get_keys;
FOR i IN 1..keys.COUNT LOOP
key_string := keys(i);
value_string := jo.get(key_string).to_string;
END LOOP;
Parsing the JSON array
If the JSON_OBJECT, key-value pair has an array, like say an array for employee details.
Here the key is 'employees', and the value is the array of employees.
In this case we parse the JSON array using JSON_ARRAY_T.parse and get the array ja.
For each element j in the array, we get the object. In this example the employee object.
We use the get method of JSON_ARRAY_T, and type cast it to JSOB_OBJECT_T
ja.get(j) and assign it to a JSON object variable.
We then repeat the steps to parse a JSON object.
--assign the array to a JSON_ARRAY_T variable
l_ja := JSON_ARRAY_T.parse(value_string);
FOR j IN 0..l_ja.get_size - 1 LOOP
-- Get the individual employee object and type cast it to JSON_OBJECT_T
l_emp_obj := JSON_OBJECT_T(l_ja.get(j));
-- Retrieve the keys for the employee object (name, job)
emp_keys := l_emp_obj.get_keys;
-- Loop through the employee keys (name, job)
FOR k IN 1..emp_keys.COUNT LOOP
emp_value_string := l_emp_obj.get(emp_keys(k)).to_string;
END LOOP;
END LOOP;
key-value pairs
employee JSON array
each object of the employee JSON array
key-value pairs of each employee JSON object
Output is as below
DECLARE
jo JSON_OBJECT_T;
keys JSON_KEY_LIST;
emp_keys JSON_KEY_LIST;
key_string VARCHAR2(1000);
value_string VARCHAR2(1000);
emp_key_string VARCHAR2(1000);
emp_value_string VARCHAR2(1000);
l_ja JSON_ARRAY_T;
l_emp_obj JSON_OBJECT_T; -- For handling individual employee objects
BEGIN
-- Parse JSON object
jo := JSON_OBJECT_T.parse('{
"department": "Accounting",
"employees": [
{
"name": "Shelley,Higgins",
"job": "Accounting Manager"
},
{
"name": "William,Gietz",
"job": "Public Accountant"
}
]
}');
-- 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);
-- Check if the key is 'employees'
IF key_string = 'employees' THEN
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
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));
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)
FOR k IN 1..emp_keys.COUNT LOOP
emp_key_string := emp_keys(k);
emp_value_string := l_emp_obj.get(emp_key_string).to_string;
-- Output employee key-value pairs
DBMS_OUTPUT.put_line('Employee ' || j || ' ' || emp_key_string || ': ' || emp_value_string);
END LOOP;
END LOOP;
END IF;
END LOOP;
END;
/
Key: department
Value: "Accounting"
Key: employees
Value: [{"name":"Shelley,Higgins","job":"Accounting Manager"},{"name":"William,Gietz","job":"Public Accountant"}]
Employees JSON array: [{"name":"Shelley,Higgins","job":"Accounting Manager"},{"name":"William,Gietz","job":"Public Accountant"}]
Employee object 0 {"name":"Shelley,Higgins","job":"Accounting Manager"}
Employee 0 name: "Shelley,Higgins"
Employee 0 job: "Accounting Manager"
Employee object 1 {"name":"William,Gietz","job":"Public Accountant"}
Employee 1 name: "William,Gietz"
Employee 1 job: "Public Accountant"
One limitation in this parser is that to identify the level 2 data, I am hardcoding the Level 2 JSON tag for it.
A more generic parser should be able to do so with no hardcoded values and parse any JSON.
For a more generic parser you can refer to
Using PL/SQL Object Types for JSON Obtain all JSON keys from JSON columns
Thought for the day
Commit no offense against the public,
and do not disgrace yourself among the people.
Do not commit a sin twice;
not even for one will you go unpunished.
Do not say "He will consider the great number of my gifts,
and when I make an offering to the most High God, he will accept it`."
Sirach 7:7-9
No comments:
Post a Comment