Re: psql JSON output format

On Mon, 2024-01-08 at 18:43 +0000, Dean Rasheed wrote:
> 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.
>
> Consider, for example:
>
> CREATE TABLE foo(col json);
> INSERT INTO foo VALUES ('"str_value"');
>
> copy foo to stdout with (format json) produces this:
>
> {"col":"str_value"}
>
> which is as expected. However, psql -Jc "select * from foo" produces
>
> [
> { "col": "\"str_value\"" }
> ]
>
> 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.

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

Yours,
Laurenz Albe