How to Use JSON_OBJECT_T in PL/SQL

JSON_OBJECT_T is the PL/SQL object type for a JSON object in memory. PARSE reads JSON text into it, GET methods read values by key, PUT and REMOVE change the document, and TO_STRING or TO_JSON turn it back into text or the JSON data type. It is the way to work with JSON documents step by step in PL/SQL code.

Code for This Guide

The main examples are in the examples/pkg-json folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

v_doc := json_object_t.parse('{...}');      -- or json_object_t(json_value_of_type_json)
v_doc.get_string(key)   v_doc.get_number(key)   v_doc.get_object(key)
v_doc.put(key, value);  v_doc.remove(key);
v_doc.get_keys          v_doc.get_type(key)     v_doc.has(key)
v_doc.to_string         v_doc.to_json

Read, Change, and List a Document

Example:

declare
  v_doc  json_object_t;
  v_keys json_key_list;
begin
  v_doc := json_object_t.parse(
             '{"flight":"NM150","gate":"A1","delay":0,"crew":{"captain":"Rao"}}');
  dbms_output.put_line('flight ' || v_doc.get_string('flight') || ', delay '
                       || v_doc.get_number('delay') || ', captain '
                       || v_doc.get_object('crew').get_string('captain'));
  v_doc.put('gate', 'B12');                          -- replaces the value
  v_doc.put('delay', 45);
  v_doc.put('boarding', true);                       -- adds a key
  v_doc.remove('crew');
  v_keys := v_doc.get_keys;
  for i in 1 .. v_keys.count loop
    dbms_output.put_line('  ' || v_keys(i) || ': ' || v_doc.get(v_keys(i)).to_string
                         || ' (' || v_doc.get_type(v_keys(i)) || ')');
  end loop;
  dbms_output.put_line(v_doc.to_string);
end;
/

Output:

flight NM150, delay 0, captain Rao
  flight: "NM150" (SCALAR)
  gate: "B12" (SCALAR)
  delay: 45 (SCALAR)
  boarding: true (SCALAR)
{"flight":"NM150","gate":"B12","delay":45,"boarding":true}

PL/SQL procedure successfully completed.

• GET_STRING, GET_NUMBER, and GET_OBJECT read values, including from a nested object.
• PUT replaces an existing key's value or adds a new key, and REMOVE deletes one.
• GET_KEYS lists the keys in order, and GET_TYPE reports SCALAR, OBJECT, or ARRAY.

From and To the JSON Data Type

A JSON column is read into a JSON variable, changed through JSON_OBJECT_T, and written back with TO_JSON.

Example:

declare
  v_loyalty json;
  v_obj     json_object_t;
begin
  select loyalty into v_loyalty from customers where customer_id = 37;
  v_obj := json_object_t(v_loyalty);                    -- from the JSON type
  v_obj.put('tier', 'PLATINUM');
  v_loyalty := v_obj.to_json;                           -- back to the JSON type, for SQL
  update customers set loyalty = v_loyalty where customer_id = 37;
  dbms_output.put_line('points: ' || v_obj.get_number('points'));
  dbms_output.put_line('missing key: ' || nvl(to_char(v_obj.get_number('miles')), 'NULL'));
  v_obj.on_error(1);                                    -- raise errors instead of NULL
  dbms_output.put_line('tier as a number: ' || v_obj.get_number('tier'));
exception
  when others then
    dbms_output.put_line(sqlerrm);
end;
/
select json_value(loyalty, '$.tier') as tier from customers where customer_id = 37;
rollback;

Output:

points: 4216
missing key: NULL
ORA-40866: JSON document operation was attempted on a non-scalar node.

PL/SQL procedure successfully completed.

TIER
___________
PLATINUM

Rollback complete.

The loyalty tier is set to PLATINUM and saved, and the query reads it back before the rollback. A missing key returns NULL by default. After ON_ERROR(1), reading the text value of tier as a number raises an error, ORA-40866 here, instead of returning NULL, which makes mistakes visible.

Things to Know

• JSON_OBJECT_T works in memory; nothing is saved until you write the document back with SQL.
• ON_ERROR(0), the default, returns NULL for missing keys and wrong types; ON_ERROR(1) raises errors.
• For changes to documents stored in tables, JSON_TRANSFORM in SQL is often simpler.

Related Guides

• Oracle JSON_TRANSFORM Function
• How to Parse and Generate JSON in PL/SQL Using APEX_JSON

Conclusion

JSON_OBJECT_T parses, reads, changes, and serializes JSON objects in PL/SQL. Convert to and from the JSON data type to work with JSON columns, and turn on ON_ERROR(1) when missing keys and wrong types should raise errors.