On 11/25/23 11:21, Davin Shearer wrote:
> Hello!
>
> I'm trying to emit a JSON aggregation of JSON rows to a file using COPY
> TO, but I'm running into problems with COPY TO double quoting the
> output. Here is a minimal example that demonstrates the problem I'm
> having:
>
> I have tried to get COPY TO to copy the results to file "as-is" by
> setting the escape and the quote characters to the empty string (''),
> but they only apply to the CSV format.
>
> Is there a way to emit JSON results to file from within postgres?
> Effectively, nn "as-is" option to COPY TO would work well for this JSON
> use case.
>
Not using COPY.
See David Johnson's post for one way using the client psql.
Otherwise you will need to use any of the many ETL programs out there
that are designed for this sort of thing.
Guys, I don't get answers like that. The JSON spec is clear:
Oops, sorry, user error. --DD
PS: The JSON spec is a bit ambiguous. First it says
> Any codepoint except " or \ or control characters
And then is clearly shows \" as a valid sequence...
Sounds like JQ is too restrictive?
Or that's the double-escape that's the culprit?
i.e. \\ is in the final text, so that's just a backslash,
and then the double-quote is no longer escaped.
I've recently noticed json_agg(row_to_json(t))
is equivalent to json_agg(t)
Maybe use that instead? Does that make a difference?
I haven't noticed wrong escaping of double-quotes yet,
but then I'm using the binary mode of queries. Perhaps that matters.
On second thought, I guess that's COPY in its text modes doing the escaping?
Interesting. The text-based modes of COPY are configurable. There's even a JSON mode.
By miracle, would the JSON output mode recognize JSON[B] values, and avoid the escaping?