14.2.4.1 Parsing JSON in PL/SQL

Parse JSON text into PL/SQL JSON objects and arrays, then access their values by type.

PL/SQL offers native types JSON_OBJECT_T and JSON_ARRAY_T for working programmatically with JSON data. Create an instance by passing JSON text in a VARCHAR2 or CLOB to the type constructor function. Then as shown below you can access property values using getter functions, depending on the data type expected, and access array elements using the get() function.

declare
    l_actionitem json_object_t;
    l_team       json_array_t;
    l_member     json_object_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;
begin
   l_actionitem := json_object_t(q'~
                    {
                      "name": "Practice Using JSON in PL/SQL",
                      "team": [
                        {"role": "LEAD","name": "Georgia"},
                        {"name": "Usha","role": "MEMBER"}
                      ]
                    }
                    ~');
   l_team   := l_actionitem.get_array('team');
   l_member := treat(l_team.get(0) as json_object_t);
   -- All three print out "Georgia" 
   dbms_output.put_line(l_member.get_string('name'));
   dbms_output.put_line(treat(l_team.get(0) as json_object_t).get_string('name'));
   dbms_output.put_line(get_object(l_team,0).get_string('name'));
end;

Tip:

When calling object functions on JSON types, ensure the variable is not null. Calling an object function on a null produces the error message:

ORA-30625: method dispatch on NULL SELF argument is disallowed