14.2.4.3 Constructing JSON in PL/SQL

Build JSON objects and arrays in PL/SQL by adding properties and array elements.

You can construct JSON programmatically property by property, starting with empty JSON objects and arrays. As shown below, notice that the code first builds the team member array's contents, then puts the array in the containing object with a team property name.

declare
    l_actionitem     json_object_t := json_object_t();
    l_team           json_array_t  := json_array_t();
    l_member_usha    json_object_t;
    l_member_georgia json_object_t;
begin
   l_actionitem.put('name', 'Practice Using JSON in PL/SQL');
   l_member_georgia := json_object_t();
   l_member_georgia.put('role', 'LEAD');
   l_member_georgia.put('name', 'Georgia');
   l_team.append(l_member_georgia);
   l_member_usha := json_object_t();
   l_member_usha.put('name', 'Usha');
   l_member_usha.put('role', 'MEMBER');
   l_team.append(l_member_usha);
   -- append all array elements before adding array to object
   l_actionitem.put('team', l_team);
   -- Both print out "Georgia" 
   dbms_output.put_line(l_member_georgia.get_string('name'));
   dbms_output.put_line(treat(l_team.get(0) as json_object_t).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