When converting XML to JSON using apex_json procedures/functions like write(), you may run into the problem that arrays with a single entry are incorrectly (or maybe just inconsistently) serialized.
This is explained clearly on this page: https://tapadoo.com/regular-problem-with-xml-to-json-converters/.
Info on the apex_json procedures used can be found at https://oracle-base.com/articles/misc/apex_json-package-generate-and-parse-json-documents-in-oracle#xml-to-json.

I haven't found a solution for this yet, but I thought I'd share my example: declare cursor c1 is with x as ( select ' <company> <team> <employee> <name>Joe</name> <surname>Bloggs</surname> </employee> </team> </company>' as x1 , ' <company> <team> <employee> <name>Joe</name> <surname>Bloggs</surname> </employee> <employee> <name>Jane</name> <surname>Doe</surname> </employee> </team> </company>' as x2 from dual ) select xmltype(x.x1) as x1_xmltype , xmltype(x.x2) as x2_xmltype from x ; begin apex_json.initialize_clob_output; for r1 in c1 loop apex_json.write(r1.x1_xmltype); apex_json.write(r1.x2_xmltype); dbms_output.put_line(apex_json.get_clob_output); end loop; apex_json.free_output; end;
Gives this JSON output: {"team":{"employee":{"name":"Joe","surname":"Bloggs"}}} {"team":[{"name":"Joe","surname":"Bloggs"},{"name":"Jane","surname":"Doe"}]} I would have expected (and preferred): {"team":[{"name":"Joe","surname":"Bloggs"}]} {"team":[{"name":"Joe","surname":"Bloggs"},{"name":"Jane","surname":"Doe"}]}
I ran into this when making a template for Apex Office Print, which uses JSON input and markup. The markup for e.g. a {#data_loop}...{/data_loop} tag will fail if the loop only contains one row because of the extra "employee" element (the row will not be shown because the template markup doesn't expect that extra element).

Some methods to prettify JSON:

From Oracle 12c (note that returning clob is only available from 12.2): select json_query('{a:[1,2,3,4]}', '$' returning varchar2(4000) pretty) as pretty_varchar2 , json_query('{a:[1,2,3,4]}', '$' returning clob pretty) as pretty_clob from dual
From Oracle 19 and up: select json_serialize('{a:[1,2,3,4]}' returning varchar2 pretty truncate error on error) as pretty_varchar2 , json_serialize('{a:[1,2,3,4]}' returning clob pretty ) as pretty_clob from dual

The apex_json.write(p_cursor) procedure does a not-so-clever number conversion sometimes.
Whenever it comes across a string that it thinks looks like a number, it will convert it into a number. However, not all numbers are recognized as such, so the result is inconsistent. And any formatting you applied is then lost of course.

I suspect the internal conversion to XML done by apex_json.write is the cause here. See the Oracle documentation on this: "This procedure writes an array with all rows that the cursor returns. Each row is a separate object. If the query contains object type, collection, or cursor columns, the procedure uses write(xmltype) to generate JSON. Otherwise, it uses DBMS_SQL to fetch rows and the write() procedures for the appropriate column data types for output."

A way to fix this is to add a prefix/suffix to the number, and then later remove that - see the "k_spacer replace" in the function fn_apex_json_write_cursor in the code below.

[ { "D_TEST" : [ { "D_INPUT" : "Input: 234.00", "D_OUTPUT" : 234 }, { "D_INPUT" : "Input: 234.50", "D_OUTPUT" : 234.5 }, { "D_INPUT" : "Input: 234.56", "D_OUTPUT" : 234.56 }, { "D_INPUT" : "Input: 1,234.00", "D_OUTPUT" : "1,234.00" }, { "D_INPUT" : "Input: 1,234.50", "D_OUTPUT" : "1,234.50" }, { "D_INPUT" : "Input: 1,234.56", "D_OUTPUT" : "1,234.56" }, { "D_INPUT" : "Input: 234.00", "D_OUTPUT" : "234.00" } ] } ]

On database 18c, when executing the code below you will get a PLS-00801 internal error: declare l_clob clob; begin l_clob := json_object('key_1' value coalesce('a', 'b')); end; /* Error: ORA-06550: line 0, column 0: PLS-00801: internal error [ph2exp:coalesce] ORA-06550: line 4, column 3: PL/SQL: Statement ignored */
On database 19c and up, this works OK.
A simple fix for 18c is to use nvl instead of coalesce.

Turns out that the path in json_table is case-sensitive - I had no idea...

For example: select * from json_table ( some_clob, '$[*]' columns name varchar2(512 char) path Name ) is not the same as select * from json_table ( some_clob, '$[*]' columns name varchar2(512 char) path name )
Personally I always use the full syntax with quotes, like this: select * from json_table ( some_clob, '$[*]' columns name varchar2(512 char) path '$.Name' ) So I never noticed this until I reformatted some old code using the Beautifier in PL/SQL Developer.
For some reason (I would call it a bug) that Beautifier changes "path Name" into "path name".
Which creates a sneaky little bug in the code, which you may not notice until much later...

More info on the path syntax at https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/function-JSON_TABLE.html#GUID-0172660F-CE29-4765-BF2C-C405BDE8369A.