Search notes:

Oracle: JSON_ARRAYAGG

json_arrayagg aggregates JSON values from multiple records into a JSON array. Thus, it lends itself to be used to turn SQL results into an array of arrays: 
select json_arrayagg(
   json_array(object_name, object_type)
   returning clob
)
from
   user_objects;
Alternatively, aggregating values returned by json_object rather than json_array returns an array of objects: 
select json_arrayagg(
   json_object(key 'name' value object_name,
               key 'type' value object_type)
   returning clob
)
from
   user_objects;

RETURNING CLOB clause

By default, json_arrayagg returns a varchar2 whose maximum length is limited.
If the length of the returned JSON fragment exceeds this length limitation, the error ORA-40478: output value too large (maximum: N) will be thrown.
The returning clob clause returns the JSON fragment as a clob which does not have such a length limit: 
json_arrayagg (
   …
   RETURNING CLOB
)

ORDER BY

When ordering the elements that json_arrayagg produces, the order by clause must be placed within the json_arrayagg function: 
select json_arrayagg(
   json_array(
      object_name,
      object_type,
      created
   )
   ORDER BY               -- <== ORDER BY is here
      object_name
   returning clob
)
from
   user_objects
;
Note, the following statement does not throw an error (at least on 19c) - but the order by clause seems to have no influence on the result set: 
select json_arrayagg(
   json_array(
      object_name,
      object_type,
      created
   )
   returning clob
)
from
   user_objects
order by
   object_name   
;

GROUP BY

select
   owner,
   json_arrayagg(json_array(
      obj.object_name,
      obj.object_type,
      obj.created
   )
   returning clob)
from
   dba_objects obj
where
   obj.owner not in ('SYS', 'SYSTEM')
group by
   owner;

NULL ON NULL clause

The null on null clause controls if aggregated null values will be added to the resulting array. 
with t as (
   select 'foo' x from dual union all
   select 'bar' x from dual union all
   select  null x from dual union all
   select 'baz' x from dual
)
select
   json_arrayagg(x               ) x,
   json_arrayagg(x null   on null) xnn,
   json_arrayagg(x absent on null) xan -- absent on null is default
from
   t;

X                    XNN                       XAN
-------------------  ------------------------  -------------------
["foo","bar","baz"]  ["foo","bar",null,"baz"]  ["foo","bar","baz"]

Returning an empty array

Unfortunately, it doesn't seem possible to force json_arrayagg to return [] if the aggregated set is empty. Instead, it simply returns null.
The following statement returns ["*"]: 
select json_arrayagg('*' returning clob)  from dual where 1 = 1;
The following statement returns null (At least sometimes, I wished it returned []): 
select json_arrayagg('*' returning clob)  from dual where 1 = 0;

Index