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_jsonRead, 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
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.
