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

user_14402246 0
Viewed 1619

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

dataformat postgresql sql json
1 Answers

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

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

Demo on DB Fiddle:

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

Note that this works on jsonb datatype only. If your input is json, you need to cast it first.

Also, NOTE: the resulting output looks like json, but is actually a string. So, you'd need to cast it back to jsonb (or json) to use any other json/jsonb functions on it.

user_106767160
Related