On Mon, Nov 27, 2023 at 10:33 AM Dominique Devienne <ddevie...@gmail.com> wrote:
> On Sat, Nov 25, 2023 at 10:00 PM Adrian Klaver <adrian.kla...@aklaver.com> > wrote: > >> 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?