Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
0


create table tab as 
    select level col1, 'value '||level col2 from dual connect by level <= 2 
2 rows affected

select max (rownum) rn, json_arrayagg (
    json_object (
        key 'col1' value col1,
        key 'col2' value col2
    ) format json returning clob 
) as json_doc
from tab


RN
JSON_DOC


2
[{"col1":1,"col2":"value 1"},{"col1":2,"col2":"value 2"}]


select json_arrayagg (  
    json_object (*) returning clob   
) as json_doc  
from tab


JSON_DOC


[{"COL1":1,"COL2":"value 1"},{"COL1":2,"COL2":"value 2"}]