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?

Does this answer your question? [SQL: What is the equivalent of json\_pretty() in postgreSQL](https://stackoverflow.com/questions/64230223/sql-what-is-the-equivalent-of-json-pretty-in-postgresql) — Zegarek, Sep 01 '23 at 08:23
That answers a question that a MySQL user, familiar with the json_pretty() function in MySQL, might have. My question is meant to be what I came looking for--how do I pretty-print json in Postgres. I don't think the question you linked should be altered, because "how do I do X from mysql in postgres" is an exceedingly common thing for people to ask. Also, search results for this were all over the place. I put in a question and answer that matched what I was looking for and will hopefully match the *search* of future seekers. — msouth, Sep 03 '23 at 04:23

score 0 · Answer 1 · answered Aug 31 '23 at 20:34

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

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

1 Answers1