Are you looking to take the members of a nested table and create a JSON array from them? That is exactly what JSON_ARRAYAGG does. Something like this:

create type codelist is table of varchar2(2);
/

Type CODELIST compiled



select json_object('codelist' : json_arrayagg(column_value)) as json_codelist
from   codelist('AB', 'CD', 'EF')
;

JSON_CODELIST                 
------------------------------
{"codelist":["AB","CD","EF"]}

Note that strings in JSON are enclosed in double-quotes - what you show as your "desired output" is not valid JSON.

Another example, to illustrate several other things:

select json_object('numberlist' : json_arrayagg(column_value)) as json_numberlist
from   sys.odcinumberlist(33,2,-89, null, -1.23, 1.3e3);

JSON_NUMBERLIST                         
----------------------------------------
{"numberlist":[33,2,-89,-1.23,1300]}

This shows that the same approach works for varrays, not just for nested tables; it works for numeric data type (which is supported by JSON, different from "string"); and it works for system-maintained types like sys.odcinumberlist, not just for user-defined types.

You can see also how NULL is handled: it is ignored, just like in all other aggregate functions. However, if you want to insert a JSON null value instead, that can be done too - the JSON generating functions have an option for that:

select json_object('numberlist' : json_arrayagg(column_value null on null)) as json_numberlist
from   sys.odcinumberlist(33,2,-89, null, -1.23, 1.3e3);

JSON_NUMBERLIST                             
--------------------------------------------
{"numberlist":[33,2,-89,null,-1.23,1300]}

But you dont need do this

demo@PDB1> select json_object('codelist' : json_arrayagg(column_value)) as json_codelist
  2  from   codelist('AB', 'CD', 'EF') ;

JSON_CODELIST
------------------------------
{"codelist":["AB","CD","EF"]}

Since 19c got UDT <-> JSON we can do it like this

demo@PDB1> select json_object( 'codelist' value codelist('AB', 'CD', 'EF') ) as json_codelist from dual;

JSON_CODELIST
------------------------------
{"codelist":["AB","CD","EF"]}

Right.

The only thing I don't know how to do, in that syntax (not sure it is even possible) is the "absent on null" option when converting a collection to a JSON array. As far as I can tell, the only option is "null on null".

but "null on null" and "absent on null" even works on collect too.

demo@PDB1> select json_object( 'codelist' value codelist('AB', 'CD', null,'EF') null on null ) as json_codelist from dual;

JSON_CODELIST
------------------------------------------------------------
{"codelist":["AB","CD",null,"EF"]}

demo@PDB1> select json_object( 'codelist' value codelist('AB', 'CD', null,'EF') absent on null ) as json_codelist from dual;

JSON_CODELIST
------------------------------------------------------------
{"codelist":["AB","CD","EF"]}