Re: psql JSON output format

From: Christoph Berg <myon(at)debian(dot)org>
To: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, Stefan Keller <sfkeller(at)gmail(dot)com>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>, Jelte Fennema-Nio <postgres(at)jeltef(dot)nl>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Joe Conway <mail(at)joeconway(dot)com>
Subject: Re: psql JSON output format
Date: 2024-01-23 15:36:58
Message-ID: Za_dGsUW4qjOf-E7@msg.df7cb.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Re: Laurenz Albe
> I am kind of unhappy about this change. It seems awkward and undesirable
> so have JSON values decorated with weird quoting in JSON output.
> I understand the motivation, but I bet it's not what will make users
> happy.

Well, why stop at JSON, and not represent any array type as a JSON
array? Compound types could be transformed into JSON objects. Custom
data types could add hooks for their own custom JSON representation.

I'd just stop the flood right before it starts...

The real reason I'd not want to go that route is because I need the
format to be round-trip safe for the "\gedit" patch, see the other
thread. We would have to transform JSON sub-parts back into PG's text
format. But there were already complaints that other patch is complex.
At the moment it's just strcmp-ing the text value before and after,
which is straighforward.

> If you need to disambiguate between SQL NULL and JSON null, my
> preferred solution would be to omit SQL NULL columns from the output
> altogether.

That works, but only by convention only.

Re: David G. Johnston
> I agree on distinguishing SQL via omission but I do think, almost
> regardless, that the output should include a metadata section that lists
> all of the actual columns in the result, the column position, and since we
> have the info available, the data type name and possibly OID. Then any
> column name present in the metadata but that isn't a key name for a given
> object is known to have an SQL NULL as the value of that column in that row.

There are no comments in JSON.

Adding the info in-band would break the simple use case of using the
data as-is for further processing.

Christoph

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Laurenz Albe 2024-01-23 15:39:18 Re: psql JSON output format
Previous Message David G. Johnston 2024-01-23 15:01:46 Re: psql JSON output format