Pretty print JSON strings in Oracle 12.2 the easy way

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

It turns out that in Oracle 12.2 you can easily get pretty printed large JSON strings using a query like this:

select json_query(large_json_value, '$' returning clob pretty) as json_clob
from some_table;

Note however that the syntax returning clob pretty is not mentioned in the documentation, where returning varchar2(n) pretty (with n <= 4000) seems to be the only valid syntax.


I really appreciated the hidden feature because now I can quickly export a bunch of JSON strings  automatically generated with views using nested JSON_ARRAYAGG and JSON_OBJECT SQL functions.