Storing and Accessing JSON Type Data

Oracle 26ai JSON type to store, query, and transform JSON, with easy access from JSON_OBJECT_T when needed.

Oracle 26ai has a JSON data type for optimized storage and access of JSON data in SQL and PL/SQL. You can use JSON columns in relational tables, create JSON collection tables with their single DATA column of JSON type, and more easily and efficiently manipulate JSON in using SQL. For example, by working with the JSON data using a JSON type you can use the powerful JSON_TRANSFORM function to do bulk, conditional modification of JSON. Consider starting with a JSON document in a JSON variable like this:

l_actionitem json :=
             json(q'~
                {
                  "name": "Practice Using JSON in PL/SQL",
                  "team": [
                    {"name": "Georgia","role": "LEAD"},
                    {"name": "Quinn"},
                    {"name": "Zelda"}
                  ]
                }
              ~');

Using JSON_TRANSFORM you can default the role property to the value MEMBER on any team array object where role is null or is missing using the following declarative operation:

-- Default role to member if not supplied
select
   json_transform(l_actionitem,
      set '$.team[*]?(@.role == null || !exists(@.role)).role'
           = 'MEMBER')
    into l_actionitem;

You can use the full power of JSON path expressions to access any data in the document right from PL/SQL:

-- Print out the name of the LEAD team member
p(json_value(l_actionitem,'$.team[*]?(@.role == "LEAD").name'));

You can loop over array objects and process them in a cursor for loop:

-- Process each team member object in a loop
for k in (select team_member
            from json_table(l_actionitem,'$.team[*]'
                    columns (team_member json path '$')))
loop
    p(json_value(k.team_member,'$.name')||','||
      json_value(k.team_member,'$.role'));
end loop;

You can selectively remove elements:

-- Delete team member Zelda using SQL
select json_transform(l_actionitem,
            remove '$.team[*]?(@.name == "Zelda" && @.role == "MEMBER")')
  into l_actionitem;

When needed, the JSON type interoperates seamlessly with the JSON_OBJECT_T API you may already be familiar with. Simply pass the JSON object to the JSON_OBJECT_T constructor function to work with it using that “interface” instead. Then later you can call TO_JSON on the JSON_OBJECT_T to get it back as a JSON type again. This example shows this back and forth in action:

-- Delete team member Quinn using PL/SQL JSON_OBJECT_T
l_actionitem_obj := json_object_t(l_actionitem);
l_teammembers    := l_actionitem_obj.get_array('team');
-- iterate backwards for deleting
for j in reverse 0..l_teammembers.get_size - 1  loop
    if get_object(l_teammembers,j).get_string('name') = 'Quinn' then
        l_teammembers.remove(j);
    end if;
end loop;
-- Show interoperability between JSON and JSON_OBJECT_T
l_actionitem := l_actionitem_obj.to_json;

The full code example follows below:

declare
    l_actionitem json :=
                 json(q'~
                    {
                      "name": "Practice Using JSON in PL/SQL",
                      "team": [
                        {"name": "Georgia","role": "LEAD"},
                        {"name": "Quinn"},
                        {"name": "Zelda"}
                      ]
                    }
                  ~');
    l_actionitem_obj json_object_t;
    l_teammembers    json_array_t;
    -----------------------------
    function get_object(
        p_array in json_array_t,
        p_index in pls_integer)
        return     json_object_t
    is
    begin
        return treat(p_array.get(p_index) as json_object_t);
    end get_object;
    -----------------------------
    procedure p(p_str in varchar)
    is
    begin
        dbms_output.put_line(p_str);
    end p;
begin
    -- Default role to member if not supplied
    select json_transform(l_actionitem,
                set '$.team[*]?(@.role == null
                                ||!exists(@.role))
                                .role' = 'MEMBER')
    into l_actionitem;
    p(json_serialize(l_actionitem pretty));
    -- Print out the name of the LEAD team member
    p(json_value(l_actionitem,'$.team[*]?(@.role == "LEAD").name'));
    -- Process each team member object in a loop
    for k in (select team_member
                from json_table(l_actionitem,'$.team[*]'
                        columns (team_member json path '$')))
    loop
        p(json_value(k.team_member,'$.name')||','||
          json_value(k.team_member,'$.role'));
    end loop;
    -- Delete team member Zelda using SQL
    select json_transform(l_actionitem,
                remove '$.team[*]?(@.name == "Zelda" && @.role == "MEMBER")')
      into l_actionitem;
    p(json_serialize(l_actionitem pretty));
    -- Delete team member Quinn using PL/SQL JSON_OBJECT_T
    l_actionitem_obj := json_object_t(l_actionitem);
    l_teammembers    := l_actionitem_obj.get_array('team');
    -- iterate backwards for deleting
    for j in reverse 0..l_teammembers.get_size - 1  loop
        if get_object(l_teammembers,j).get_string('name') = 'Quinn' then
            l_teammembers.remove(j);
        end if;
    end loop;
    -- Show interoperability between JSON and JSON_OBJECT_T
    l_actionitem := l_actionitem_obj.to_json;
    p(json_serialize(l_actionitem pretty));
end;

Running the PL/SQL block above produces the following output:

{
  "name" : "Practice Using JSON in PL/SQL",
  "team" :
  [
    {
      "name" : "Georgia",
      "role" : "LEAD"
    },
    {
      "name" : "Quinn",
      "role" : "MEMBER"
    },
    {
      "name" : "Zelda",
      "role" : "MEMBER"
    }
  ]
}
Georgia
Georgia,LEAD
Quinn,MEMBER
Zelda,MEMBER
{
  "name" : "Practice Using JSON in PL/SQL",
  "team" :
  [
    {
      "name" : "Georgia",
      "role" : "LEAD"
    },
    {
      "name" : "Quinn",
      "role" : "MEMBER"
    }
  ]
}
{
  "name" : "Practice Using JSON in PL/SQL",
  "team" :
  [
    {
      "name" : "Georgia",
      "role" : "LEAD"
    }
  ]
}