JSON output from psql

Hi

pá 8. 5. 2020 v 20:18 odesílatel Gurjeet Singh <gurjeet@singh.im> napsal:

psql currently supports HTML, CSV, etc output formats. I was wondering
if supporting JSON format was requested or discussed in past. If there's
desire for this feature, perhaps we can add it to the TODO list on wiki so
someone can pick it up and work on it in future.

is there some standardised format for output table?

Pavel

On Fri, May 8, 2020 at 12:01 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

pá 8. 5. 2020 v 20:18 odesílatel Gurjeet Singh <gurjeet@singh.im> napsal:

psql currently supports HTML, CSV, etc output formats. I was
wondering if supporting JSON format was requested or discussed in past. If
there's desire for this feature, perhaps we can add it to the TODO list on
wiki so someone can pick it up and work on it in future.

is there some standardised format for output table?

I see "-T, --table-attr=TEXT" option in `psql --help` output, presumably
that's to create HTML tables.

Best regards,
--
Gurjeet Singh http://gurjeet.singh.im/

pá 8. 5. 2020 v 21:08 odesílatel Gurjeet Singh <gurjeet@singh.im> napsal:

On Fri, May 8, 2020 at 12:01 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

pá 8. 5. 2020 v 20:18 odesílatel Gurjeet Singh <gurjeet@singh.im> napsal:

psql currently supports HTML, CSV, etc output formats. I was
wondering if supporting JSON format was requested or discussed in past. If
there's desire for this feature, perhaps we can add it to the TODO list on
wiki so someone can pick it up and work on it in future.

is there some standardised format for output table?

I see "-T, --table-attr=TEXT" option in `psql --help` output, presumably
that's to create HTML tables.

I though for JSON format. This format is too generic.

Pavel

On Fri, May 8, 2020 at 12:10 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

pá 8. 5. 2020 v 21:08 odesílatel Gurjeet Singh <gurjeet@singh.im> napsal:

On Fri, May 8, 2020 at 12:01 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

pá 8. 5. 2020 v 20:18 odesílatel Gurjeet Singh <gurjeet@singh.im>
napsal:

psql currently supports HTML, CSV, etc output formats. I was
wondering if supporting JSON format was requested or discussed in past. If
there's desire for this feature, perhaps we can add it to the TODO list on
wiki so someone can pick it up and work on it in future.

is there some standardised format for output table?

I see "-T, --table-attr=TEXT" option in `psql --help` output, presumably
that's to create HTML tables.

I though for JSON format. This format is too generic.

I think I misunderstood your question earlier.

There's no standard format that comes to mind, but perhaps an output format
similar to that of (array of row_to_json()) would be desirable. For
example, `select relname, relnamespace from pg_class;` would emit the
following:

[
{"relname": "pgclass", "relnamespace": 11},
{"relname": "pg_statistic", "relnamespace": 11},
]

Best regards,
--
Gurjeet Singh http://gurjeet.singh.im/

On Fri, May 8, 2020 at 7:32 PM Gurjeet Singh <gurjeet@singh.im> wrote:

There's no standard format that comes to mind, but perhaps an output format similar to that of (array of row_to_json()) would be desirable. For example, `select relname, relnamespace from pg_class;` would emit the following:

[
{"relname": "pgclass", "relnamespace": 11},
{"relname": "pg_statistic", "relnamespace": 11},
]

I don't see why psql needs any special support. You can already
generate this using the existing server side functions, if you want
it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, May 11, 2020 at 1:24 PM Robert Haas <robertmhaas@gmail.com> wrote:

On Fri, May 8, 2020 at 7:32 PM Gurjeet Singh <gurjeet@singh.im> wrote:

There's no standard format that comes to mind, but perhaps an output

format similar to that of (array of row_to_json()) would be desirable. For
example, `select relname, relnamespace from pg_class;` would emit the
following:

[
{"relname": "pgclass", "relnamespace": 11},
{"relname": "pg_statistic", "relnamespace": 11},
]

I don't see why psql needs any special support. You can already
generate this using the existing server side functions, if you want
it.

That's a good point! It might still be desirable, perhaps for performance
trade-off of JSON conversion on the client-side instead of on the
server-side.

Best regards,
--
Gurjeet Singh http://gurjeet.singh.im/

On Mon, May 11, 2020 at 4:42 PM Gurjeet Singh <gurjeet@singh.im> wrote:

That's a good point! It might still be desirable, perhaps for performance trade-off of JSON conversion on the client-side instead of on the server-side.

If there's a performance problem with the server's code here, we
should probably try to fix it, instead of adding the same feature on
the client side.

But also, we shouldn't start by deciding we need feature X and then
looking for the reason why we need it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, May 13, 2020 at 12:50 PM Robert Haas <robertmhaas@gmail.com> wrote:

On Mon, May 11, 2020 at 4:42 PM Gurjeet Singh <gurjeet@singh.im> wrote:

That's a good point! It might still be desirable, perhaps for performance trade-off of JSON conversion on the client-side instead of on the server-side.

If there's a performance problem with the server's code here, we
should probably try to fix it, instead of adding the same feature on
the client side.

Performance problem is not just about how much CPU/RAM is used on
server-side but other resources like network consumption to get the
results to the client. Anecdotally, I have heard of a case where
Oracle implemented custom Huffman Encoding for a customer to speed up
delivery of their resultset that contained just rows of true/false.

Arguably, delivering JSON (with its repeating attribute names in every
element of the array, dquotes and commas) is more network intensive
than converting the resultset to JSON on network side.

But also, we shouldn't start by deciding we need feature X and then
looking for the reason why we need it.

That's better than, or at least on par with, the excuses like "We
should do it because some other database does it, too" :-)

Best regards,
--
Gurjeet Singh http://gurjeet.singh.im/

On Wed, May 13, 2020 at 1:14 PM Gurjeet Singh <gurjeet@singh.im> wrote:

Arguably, delivering JSON (with its repeating attribute names in every
element of the array, dquotes and commas) is more network intensive
than converting the resultset to JSON on network side.

s/network side/client side/

Best regards,
--
Gurjeet Singh http://gurjeet.singh.im/

On 05/13/20 16:16, Gurjeet Singh wrote:

On Wed, May 13, 2020 at 1:14 PM Gurjeet Singh <gurjeet@singh.im> wrote:

Arguably, delivering JSON (with its repeating attribute names in every
element of the array, dquotes and commas) is more network intensive
than converting the resultset to JSON on [client] side.

Does this suggest perhaps some sort of hybrid approach, where jsonbc
could be available as a binary on-the-wire format and the client only
needs the ability to deparse it: a query on the server could marshal
the results into that form, the client negotiates the binary transfer
format, and deparses to normal JSON syntax on its end?

It seems the server-side "compression" to jsonbc should be optimizable
when what is happening is marshaling of a tabular result: what the
repeating keys are going to be is known up front.

Maybe could use a transient (or session lifetime?) 'external' dictionary
that gets generated and sent to the client. but not stored in
pg_jsonb_dict?

Seems like a lot of work just to get json-shaped query results from psql,
but maybe the ability to receive jsonbc on the wire would be of interest
to drivers generally.

Regards,
-Chap

On Wed, May 13, 2020 at 2:01 PM Chapman Flack <chap@anastigmatix.net> wrote:

Seems like a lot of work just to get json-shaped query results from psql,

+1. If we look at the amount of work needed for the hybrid approach
you describe, compared to running CSV result through something like
csv2json, there's a 100% chance of the idea being shot down :-)

but maybe the ability to receive jsonbc on the wire would be of interest
to drivers generally.

I'm not sure of that, but then I don't have visibility into the needs
of consumers of our drivers.

Best regards,
--
Gurjeet Singh http://gurjeet.singh.im/