Thread: JSON output from psql
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.
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:
Hipá 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:Hipá 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:Hipá 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.
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-sideinstead 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-sideinstead 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/