JSON_ARRAY_T is the PL/SQL type for a JSON array. It builds arrays element by element, reads elements by position, inserts and removes them, and serializes the result. Elements can be scalars, objects, other arrays, or null, mixed in one array.
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_list := json_array_t(); -- or json_array_t.parse('[...]')
v_list.append(value); v_list.append_null;
v_list.put(position, value); -- insert before position
v_list.remove(position);
v_list.get(position) -- positions start at 0
v_list.get_number(position) v_list.get_string(position)
v_list.get_size v_list.to_stringBuild an Array of Objects
Example:
declare
v_list json_array_t := json_array_t();
v_item json_object_t;
v_total number := 0;
begin
for r in (select airport_code, city from airports
where country_code = 'IN' order by 1) loop
v_item := json_object_t();
v_item.put('code', r.airport_code);
v_item.put('city', r.city);
v_list.append(v_item);
end loop;
v_list.append('more to come'); -- arrays can mix types
dbms_output.put_line(v_list.get_size || ' elements, the last '
|| v_list.get(v_list.get_size - 1).to_string);
for i in 0 .. v_list.get_size - 1 loop -- positions start at 0
if v_list.get(i).is_object then
v_item := treat(v_list.get(i) as json_object_t);
dbms_output.put_line(' ' || i || ': ' || v_item.to_string);
end if;
end loop;
end;
/Output:
4 elements, the last "more to come"
0: {"code":"BLR","city":"Bengaluru"}
1: {"code":"BOM","city":"Mumbai"}
2: {"code":"DEL","city":"Delhi"}
PL/SQL procedure successfully completed.Each Indian airport becomes a JSON_OBJECT_T appended to the array, followed by a plain string. GET with position GET_SIZE - 1 returns the last element, since positions start at 0. IS_OBJECT checks an element's kind, and TREAT turns the generic element into a JSON_OBJECT_T.
Parse, Sum, Insert, and Remove
Example:
declare
v_delays json_array_t := json_array_t.parse('[45, 0, 120, 15]');
v_total number := 0;
begin
for i in 0 .. v_delays.get_size - 1 loop
v_total := v_total + v_delays.get_number(i);
end loop;
dbms_output.put_line('total delay: ' || v_total || ' minutes');
v_delays.put(0, 5); -- insert at position 0
v_delays.remove(3); -- remove the element at position 3
v_delays.append_null;
dbms_output.put_line(v_delays.to_string);
dbms_output.put_line('position 4 is null? '
|| case when v_delays.get(4).is_null then 'yes' end);
end;
/Output:
total delay: 180 minutes [5,45,0,15,null] position 4 is null? yes PL/SQL procedure successfully completed.
The parsed delays add up to 180 minutes. PUT at position 0 inserts 5 before the first element, REMOVE(3) drops 120, and APPEND_NULL adds a JSON null, which IS_NULL detects.
Things to Know
- Positions are zero-based, unlike PL/SQL collections, which start at 1.
- GET returns a JSON_ELEMENT_T; use TREAT to get an object or array, or the typed GET_ methods for scalars.
- To turn a query result into a JSON array, JSON_ARRAYAGG in SQL is faster than appending in a loop.
Related Guides
Conclusion
JSON_ARRAY_T builds, reads, and edits JSON arrays in PL/SQL with zero-based positions. Append objects and scalars, insert and remove by position, check element kinds with IS_OBJECT and IS_NULL, and serialize with TO_STRING.
