Re: psql JSON output format

On Tue, 9 Jan 2024 at 09:43, Christoph Berg <myon(at)debian(dot)org> wrote:
>
> I can see we probably wouldn't want two different output formats named
> json, but the general idea of "allow psql to format results as json of
> strings" makes a lot of sense, so we should try to make it work. Does
> it even have to be compatible?
>

I would say that they should be compatible, in the sense that an
external tool parsing the outputs should regard them as equal, but
maybe there are different expectations for the two features.

> I'll note that the current code uses PG's string representation of
> strings which is meant to be round-trip safe when fed back into the
> server. So quoted numeric values aren't a problem at all. (And that
> part is fixable.)
>

I'm not sure that being round-trip safe is a necessary goal here, but
again, it's about the expectations for the feature. I was imagining
that the goal was to produce something that an external tool would
parse, rather than something Postgres would read back in. So not
quoting numeric values seems desirable to produce output that better
reflects the semantic content of the data (though it doesn't affect it
being round-trip safe).

> The real problem here is that COPY json violates that by pulling json
> values up one syntax level. "Normal" cases will be fixable by just
> looking for json(b) and printing that unquoted. And composite types
> with jsonb members... are these really only half-quoted?!
>

What to do with composites is an interesting point in question. COPY
format json will turn a composite into a JSON object whose keys are
the field names. That's useful if you want to use an external tool to
parse the result and get at the individual fields, but it's not
round-trip safe. OTOH, this patch outputs the Postgres string
representation of the object, which might be round-trip safe, but is
not very convenient for any other tool to read.

Regards,
Dean