How can I get pretty printed JSON from a Postgres SQL query in psql?

Question

I have JSON data stored as regular strings in my Postgres tables. When I select them in the psql client, I want these strings to be pretty-printed so that I can see the data more easily.

Can I do this without running the output through an external program?

Answer 1

You can use the function jsonb_pretty--just cast the data to type jsonb first:

with data (text_json_blob) as (
  values
    ('{"something":"simple"}'),
    ('{"foo":"bar", "qux":["toto","tata","titi","tutu"]}')
)
select
  jsonb_pretty(text_json_blob :: jsonb) as nice_json
from
  data;

Output (I started the client with psql -A to strip some extraneous output):

nice_json
{
    "something": "simple"
}
{
    "foo": "bar",
    "qux": [
        "toto",
        "tata",
        "titi",
        "tutu"
    ]
}
(2 rows)

jsonb_pretty was added in Postgres 9.5