How to pretty format JSON in Oracle?

Question

I wanted to know if there is any way to format a JSON in Oracle (as does this web site example)

In XML I used:

SELECT XMLSERIALIZE(Document XMLTYPE(V_RESPONSE) AS CLOB INDENT SIZE = 2)
INTO V_RESPONSE
FROM DUAL;     

And it works very well.

Answer 1

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"          |
    }                     |
  ]                       |
}                         |

Answer 2

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,
       js
from   t;

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