A single query to decompose JSON using the APEX_JSON package, part 2

In my previous blog post, I described how to decompose a JSON document with a single query. I used the APEX_JSON package, and with that I could keep the code quite simple. One drawback, however, was that the query results were in alphabetical order, instead of the order in the JSON document.
The GET_MEMBERS method
In part 1 I explained the G_VALUES package variable, which is of type associative array. Therefore, a query on this variable always returns results in the order of the index, which in this case is a VARCHAR2(32767). In the query I used the NUMBER_VALUE and VARCHAR2_VALUE parts of the T_VALUE record type. But there is also an OBJECT_MEMBERS part, which is of type APEX_T_VARCHAR2, which is a nested table. Since nested tables are not indexed, but remain in the order that they are created, could I use that in my query?
Luckily, the answer is Yes. The APEX_JSON package offers the GET_MEMBERS function, which is equivalent to the OBJECT_MEMBERS part of the T_VALUE type. The input is the JSON path of the object, and it returns an APEX_T_VARCHAR2 nested table. If I try this for the "wand" object in the first object of the Harry Potter API (https://hp-api.onrender.com/),
"wand":{"wood":"holly","core":"phoenix tail feather","length":11}
I get the following result:
declare
l_clob clob;
begin
select json_column
into l_clob
from my_table
where id = 1;
apex_json.parse(l_clob);
dbms_output.put_line(
apex_string.join(apex_json.get_members('[1].wand')
,','));
end;
/
wood,core,length
So, this function indeed returns the object members in the order that they are in the JSON, and not in alphabetical order.
Objects within objects within objects
Now that I can get the members of an object, I can construct the path of all the members. I just take the path of the object, then add a dot and the name of the member. Then I can retrieve the member kind (varchar2, number, object etcetera) using APEX_JSON.GET_VALUE_KIND, and if I know the kind I can call the appropriate function to get the value, so APEX_JSON.GET_VARCHAR2, APEX_JSON.GET_NUMBER and of course, if it is an object, APEX_JSON.GET_MEMBERS. But those members can again be objects, etcetera, so it would be logical to make our query recursive.
So the basic query will be like this:
with function get_jsonvalues(in_json in clob
,in_separator in varchar2
,in_path in varchar2 default '.'
,in_level in number default 1) return apex_t_varchar2 as
l_values apex_t_varchar2 := apex_t_varchar2();
l_kind binary_integer;
begin
if in_level = 1 then
apex_json.parse(in_json);
end if;
l_kind := apex_json.get_value_kind(p_path => in_path);
if l_kind not in (apex_json.c_object, apex_json.c_array) then
-- Create a new entry in l_values for the field
else
-- Create a new entry in l_values for the object/array
-- Then call get_jsonvalues for each member
end if;
return l_values;
end;
sep as (select '==' arator from dual)
select substr(column_value,1,instr(column_value,sep.arator,1)-1) json_path
, substr(column_value,instr(column_value,sep.arator,1)+length(sep.arator)) value
from sep
cross
join get_jsonvalues((select json_column
from my_table
where id = 1)
,sep.arator
);
The framework is the same as in the previous query:
I use an inline table function that parses the JSON. This function constructs an APEX_T_VARCHAR2 nested table, with a separator to separate field and value, and I select from that function using the JSON column in MY_TABLE as the first argument.
The function now has two additional parameters:
in_path, which is the current path in my recursive tree walk. The root path is
'.', which is the default, and is implicitly used in the SELECT statement.in_level, which is the level in my recursive tree walk. The default is 1. This is necessary because the JSON only has to be parsed once. Each recursive call to the procedure increases the level by 1, so the JSON will be parsed at the first call of the function, level 1, and is never parsed again.
In the function, after parsing the JSON, I first determine the kind of the passed path. Then there are two possibilities:
The current path is not an object or array. I just create the row in the nested table for the current field and return from the function.
The current path is an object or an array. I create the row in the nested table for the current object or array. Then I get the members for the object (using the APEX_JSON.GET_MEMBERS function) or the number of elements in the array (using the APEX_JSON.GET_COUNT function). The object members I store in the l_members variable, which is of type APEX_T_VARCHAR2. Then I loop through the l_members variable, or from 1 to the total number in the array, construct the total path of the member or element, and call my GET_JSONVALUES with the constructed path, and the level increased by 1. The result that is returned from the function should be combined with the nested table that is built in this function. For this the MULTISET UNION operator is used, which puts two nested tables together into one.
The total query will now be:
with function get_jsonvalues(in_json in clob
,in_separator in varchar2
,in_path in varchar2 default '.'
,in_level in number default 1) return apex_t_varchar2 as
l_members apex_t_varchar2;
l_values apex_t_varchar2 := apex_t_varchar2();
l_base_path varchar2(4000 char);
l_path varchar2(4000 char);
l_kind binary_integer;
l_array_count binary_integer;
begin
if in_level = 1 then
apex_json.parse(in_json);
end if;
l_kind := apex_json.get_value_kind(p_path => in_path);
if l_kind not in (apex_json.c_object, apex_json.c_array) then
-- Create a new entry in l_values for the field
case l_kind
when apex_json.c_null then
l_values.extend;
l_values(l_values.count) := in_path || in_separator;
when apex_json.c_true then
l_values.extend;
l_values(l_values.count) := in_path || in_separator || 'true';
when apex_json.c_false then
l_values.extend;
l_values(l_values.count) := in_path || in_separator || 'false';
when apex_json.c_number then
l_values.extend;
l_values(l_values.count) := in_path || in_separator || apex_json.get_number(in_path);
when apex_json.c_varchar2 then
l_values.extend;
l_values(l_values.count) := in_path || in_separator || apex_json.get_varchar2(in_path);
when apex_json.c_clob then
l_values.extend;
l_values(l_values.count) := in_path || in_separator || 'clob';
end case;
else
-- Create a new entry in l_values for the object/array
-- Then call get_jsonvalues for each member
if l_kind = apex_json.c_object then
l_values.extend;
l_values(l_values.count) := in_path || in_separator || '{OBJECT}';
l_members := apex_json.get_members(in_path);
l_base_path := ltrim(in_path || '.', '.');
for i in 1 .. l_members.count loop
l_path := l_base_path || l_members(i);
l_values := l_values multiset union get_jsonvalues(in_json, in_separator, l_path, in_level + 1);
end loop;
elsif l_kind = apex_json.c_array then
l_array_count := apex_json.get_count(in_path);
l_values.extend;
l_values(l_values.count) := in_path || in_separator || 'ARRAY[' || l_array_count || ']';
l_base_path := ltrim(in_path, '.');
for i in 1..l_array_count loop
l_path := l_base_path || '[' || i || ']';
l_values := l_values multiset union get_jsonvalues(in_json, in_separator, l_path, in_level + 1);
end loop;
end if;
end if;
return l_values;
end;
sep as (select '==' arator from dual)
select substr(column_value,1,instr(column_value,sep.arator,1)-1) json_path
, substr(column_value,instr(column_value,sep.arator,1)+length(sep.arator)) value
from sep
cross
join get_jsonvalues((select json_column
from my_table
where id = 1)
,sep.arator
);
The result of the query is:
JSON_PATH VALUE
---------------------- --------------------------------------
. ARRAY[1]
[1] {OBJECT}
[1].id 9e3f7ce4-b9a7-4244-b709-dae5c1f1d4a8
[1].name Harry Potter
[1].alternate_names ARRAY[4]
[1].alternate_names[1] The Boy Who Lived
[1].alternate_names[2] The Chosen One
[1].alternate_names[3] Undesirable No. 1
[1].alternate_names[4] Potty
[1].species human
[1].gender male
[1].house Gryffindor
[1].dateOfBirth 31-07-1980
[1].yearOfBirth 1980
[1].wizard true
[1].ancestry half-blood
[1].eyeColour green
[1].hairColour black
[1].wand {OBJECT}
[1].wand.wood holly
[1].wand.core phoenix tail feather
[1].wand.length 11
[1].patronus stag
[1].hogwartsStudent true
[1].hogwartsStaff false
[1].actor Daniel Radcliffe
[1].alternate_actors ARRAY[0]
[1].alive true
[1].image https://ik.imagekit.io/hpapi/harry.jpg
29 rows selected.
So now the fields are identically ordered as in the original JSON.
Querying part of the JSON
The query can also be used to query just a part of the JSON. In the call to GET_JSONVALUES in the SELECT statement you just have to include a value for the in_path parameter (in the above query it implicitly uses the default value '.'). For example, if the last part of the query would be
cross
join get_jsonvalues((select json_column
from my_table
where id = 1)
,sep.arator
,'[1].wand'
);
Only the wand object of the first array element would be queried. The result would then be:
JSON_PATH VALUE
--------------- --------------------
[1].wand {OBJECT}
[1].wand.wood holly
[1].wand.core phoenix tail feather
[1].wand.length 11
Conclusion
It is also possible to use a single query to decompose JSON, where the order of the JSON is preserved. Again, the APEX_JSON package can be used, but instead of simply returning the G_VALUES package variable, an inline table function has to be used that is called recursively, and that makes use of many of the available functions in the APEX_JSON package. An additional advantage of using this recursive method is that it is quite easy to decompose just a part of the JSON.
Comments
Join the discussionNo comments yet. Be the first to comment.





