public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dominique Devienne <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: Davin Shearer <[email protected]>
Cc: [email protected]
Subject: Re: Emitting JSON to file using COPY TO
Date: Mon, 27 Nov 2023 10:44:55 +0100
Message-ID: <CAFCRh--Vhx0105S7SWAYHGcVMTSRtbcoArDUGSbYSWRUGunW_g@mail.gmail.com> (raw)
In-Reply-To: <CAFCRh-_LhP5Ln4-beVz=1pQ9VycU3hAxnJHXhPHZRmX0BSCVEQ@mail.gmail.com>
References: <CALvfUkBxTYy5uWPFVwpk_7ii2zgT07t3d-yR_cy4sfrrLU=kcg@mail.gmail.com>
	<[email protected]>
	<CAFCRh-_LhP5Ln4-beVz=1pQ9VycU3hAxnJHXhPHZRmX0BSCVEQ@mail.gmail.com>

On Mon, Nov 27, 2023 at 10:33 AM Dominique Devienne <[email protected]>
wrote:

> On Sat, Nov 25, 2023 at 10:00 PM Adrian Klaver <[email protected]>
> 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?

view thread (35+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Emitting JSON to file using COPY TO
  In-Reply-To: <CAFCRh--Vhx0105S7SWAYHGcVMTSRtbcoArDUGSbYSWRUGunW_g@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox