Re: psql JSON output format

Re: Dean Rasheed
> I can see the appeal in this feature. However, as it stands, this
> isn't compatible with copy format json, and I think it would need to
> duplicate quite a lot of the JSON output code in client-side code to
> make it compatible.

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?

If the code required is the same, it could be moved to libpgcommon.

> The problem is, various datatypes such as boolean, number types, json,
> and jsonb must not be quoted and escaped, since that would change them
> to strings or double-encode them in the result. And then there are
> domain types built on top of those types, and arrays, etc. See, for
> example, the logic in json_categorize_type(). I think that trying to
> duplicate that client-side is doomed to failure.

Can we try to make it work first, before we declare the perfect the
enemy of the good?

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.)

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?!

Re: Laurenz Albe
> > The problem is, various datatypes such as boolean, number types, json,
> > and jsonb must not be quoted and escaped, since that would change them
> > to strings or double-encode them in the result.
>
> I agree that such data types should not be double quoted.

I left that out so far because it didn't make a practical difference,
but that's fixable.

> > And then there are
> > domain types built on top of those types, and arrays, etc. See, for
> > example, the logic in json_categorize_type(). I think that trying to
> > duplicate that client-side is doomed to failure.
>
> Perhaps. But maybe "printTableContent" could be extended to contain
> a boolean array "quote_for_json" that is set in "printTableAddHeader"
> based on the underlying data type, similar to how "aligns" is set now.
> Detecting array types might be a challenge.
>
> Domains might not be a problem, since "PQftype()" seems to return the
> base data type for domain values.

Thanks, I'll give that a try.

Christoph