How to pretty format JSON in Oracle?

With Oracle 12c, you can use the JSON_QUERY() function with the RETURNING ... PRETTY clause :

PRETTY : Specify PRETTY to pretty-print the return character string by inserting newline characters and indenting

Expression :

JSON_QUERY(js_value, '$' RETURNING VARCHAR2(4000) PRETTY)

Demo on DB Fiddle :

with t as (select '{"a":1, "b": [{"b1":2}, {"b2": "z"}]}' js from dual)select json_query(js, '$' returning varchar2(4000) pretty) pretty_js, js from t;

Yields :

PRETTY_JS                 | JS--------------------------|----------------------------------------{                         | {"a":1, "b": [{"b1":2}, {"b2": "z"}]}  "a" : 1,                |   "b" :                   |  [                       |    {                     |      "b1" : 2            |     },                    |    {                     |      "b2" : "z"          |    }                     |  ]                       |}                         |

When you're lucky enough to get to Oracle Database 19c, there's another option for pretty printing: JSON_serialize.

This allows you to convert JSON between VARCHAR2/CLOB/BLOB. And includes a PRETTY clause:

with t as (  select '{"a":1, "b": [{"b1":2}, {"b2": "z"}]}' js  from dual)select json_serialize (         js returning varchar2 pretty       ) pretty_js,       jsfrom   t;PRETTY_JS               JS{                       {"a":1, "b": [{"b1":2}, {"b2": "z"}]}    "a" : 1,  "b" :  [    {      "b1" : 2    },    {      "b2" : "z"    }  ]}