psql JSON output format

From: Christoph Berg <myon(at)debian(dot)org>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: psql JSON output format
Date: 2023-12-18 14:56:28
Message-ID: ZYBdnGW0gKxXL5I_@msg.df7cb.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I noticed psql was lacking JSON formatting of query results which I
need for a follow-up patch. It also seems useful generally, so here's
a patch:

postgres=# \pset format json
Output format is json.
postgres=# select * from (values ('one', 2, 'three'), ('four', 5, 'six')) as sub(a, b, c);
[
{ "a": "one", "b": "2", "c": "three" },
{ "a": "four", "b": "5", "c": "six" }
]
postgres=# \x
Expanded display is on.
postgres=# select * from (values ('one', 2, 'three'), ('four', 5, 'six')) as sub(a, b, c);
[{
"a": "one",
"b": "2",
"c": "three"
},{
"a": "four",
"b": "5",
"c": "six"
}]
postgres=#

Both normal and expanded output format are optimized for readability
while still saving screen space.

Both formats output the same JSON structure, an array of objects.
Other variants like array-of-arrays or line-separated objects
("jsonline") might be possible, but I didn't want to overengineer it.

On the command line, the format is selected by `psql --json` and `psql -J`.
(I'm not attached to the short option, but -J was free and it's in
line with `psql -H` to select HTML.)

Christoph

Attachment Content-Type Size
v1-0001-Add-JSON-output-format-to-psql.patch text/x-diff 13.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jelte Fennema-Nio 2023-12-18 15:06:21 Re: psql JSON output format
Previous Message Stephen Frost 2023-12-18 14:39:49 Re: Detecting some cases of missing backup_label