Skip to main content

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

Updated 8 min readMarkdown
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.

💡
Technically, the order of fields within an object in a JSON document is irrelevant. Two objects with the same fields and values, but in a different order, are technically the same. From a functional perspective however, fields with the same functional meaning are often grouped together, so it can be convenient to know the order within a JSON object.

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 MY_TABLE table definition and the JSON that is inserted can be found in my previous blog post.

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.

No comments yet. Be the first to comment.