SQL: What is the equivalent of json_pretty() in postgreSQL

Question

What is the equivalent of this MySQL function

SELECT JSON_PRETTY('{"a": 1, "b": 2, "c": 3}') AS Result
  FROM table;

Formatted JSON:

+------------------------------+
| Result                       |
+------------------------------+
| {                            |
|   "a": 1,                    |
|   "b": 2,                    |
|   "c": 3                     |
| }                            |
+------------------------------+

I've tried jsonb_pretty() as mentioned in the document but nothing is available

Answer 1

You want jsonb_pretty(), that is available in Postgres since version 9.5:

SELECT jsonb_pretty('{"a":1, "aa":2, "c":3, "b":4, "b":5}') AS result

Demo on DB Fiddle:

{
    "a": 1,
    "b": 5,
    "c": 3,
    "aa": 2
}

The jsonb type forces internal key order, so casting to it might reorder your json. It also discards whitespace between tokens and keeps only the last value for duplicate keys. Casting from json to jsonb and back will not restore the original order, whitespace or the duplicate keys.

jsonb_pretty() works on jsonb datatype only. If your input is json, you need to cast it first:

SELECT jsonb_pretty(cast(your_column as jsonb))
FROM your_table;

As a formatting function, it returns type text, similar to how MySQL's json_pretty() returns longtext. Cast it back to json/jsonb or revert to the unprocessed value to continue using json functions and operators.