Re: psql JSON output format

On Tue, Jan 23, 2024 at 7:35 AM Stefan Keller <sfkeller(at)gmail(dot)com> wrote:

> Am Di., 23. Jan. 2024 um 15:15 Uhr schrieb Laurenz Albe
> <laurenz(dot)albe(at)cybertec(dot)at>:
> > I understand the motivation, but I bet it's not what will make users
> > happy.
> >
> > 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.
>
> I fully support Laurenz's proposal and argumentation. The main use
> case for such a JSON output feature is further processing somewhere
> else.
>
> --Stefan
>
> Am Di., 23. Jan. 2024 um 15:15 Uhr schrieb Laurenz Albe
> <laurenz(dot)albe(at)cybertec(dot)at>:
> >
> > On Mon, 2024-01-22 at 16:19 +0100, Christoph Berg wrote:
> > > What I did now in v3 of this patch is to print boolean and numeric
> > > values (ints, floats, numeric) without quotes, while adding the quotes
> > > back to json. This solves the NULL vs 'null'::json problem.
> >
> > The patch is working as advertised.
> >
> > 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.
> >
> > 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.
> >
>

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.

David J.