Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r7YAz-005nAy-24 for pgsql-general@arkaria.postgresql.org; Mon, 27 Nov 2023 09:45:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r7YAx-00CJBO-F6 for pgsql-general@arkaria.postgresql.org; Mon, 27 Nov 2023 09:45:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r7YAw-00CJAT-US for pgsql-general@lists.postgresql.org; Mon, 27 Nov 2023 09:45:11 +0000 Received: from mail-oa1-x30.google.com ([2001:4860:4864:20::30]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1r7YAt-007ZwE-C0 for pgsql-general@lists.postgresql.org; Mon, 27 Nov 2023 09:45:09 +0000 Received: by mail-oa1-x30.google.com with SMTP id 586e51a60fabf-1f066fc2a2aso1981368fac.0 for ; Mon, 27 Nov 2023 01:45:07 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1701078306; x=1701683106; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=4cbeNkNQUzEi9nPcbCxi/ZR1N0JjeeXs1XORq6D1pu0=; b=avi2hqCOW7YQsxhXR17+RhMMzGnCmQYKCje4YJ3RIbWbEIu7GGtItZOcWP9gr0z1GR 4pqOiFklBaS/LKFkXTgQiEb/3AS0gH/uu6l3z2X0Z6KOFPlGXgUzDamNVsrHKD9CUOKm OGmVBDGdGRYtNsqZXbdrU3RDqUwsGP9Q84fcQDjzviVmqzY7bcSyB9bwid3+vd0bCgU0 Q5Ek6x8B6u5lQZtl7jQPlcPPN/Vo/YpTfxxwUxd7rjcqHlVmx4x8upQw1yk9xot3EWP7 lDdK53AMdIgPfYlwItIcMjvTYp6589W+0QRxv1daSwTdZqmD1EcMk1XkObPMBvjXqTAt yi+g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1701078306; x=1701683106; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=4cbeNkNQUzEi9nPcbCxi/ZR1N0JjeeXs1XORq6D1pu0=; b=jOUdEqDzKjcvQZX2kMoYXZnLYQuQ6kLkuPiiYDnz/Sjl+A3GH39LrfChrUdsXj1nWq rYRMEwYm/gxHrmo8iQNI5wegvF260mOxh/zuxqaXQXjygDqt/9OdZIB2J2FzTU6vbmr5 NhRVHEG7Fsn2KKkPSka1s0xRipaTgPSqnMzh4I9grd2jbA4F/lEul0Gplg3OORrtq09o 8XagMmkD2s5+RzPUBYrIUgYGHO19vND7Ouwp4rWWZruimEaQJsJGtfbWS8ck4xN0jvcL lZA8ZoU5o0S59298X/fSeY+kPDlj543qoqp6B72O+mmnnK0RzoyA0Gvv2if86dEKYJI+ yq0g== X-Gm-Message-State: AOJu0YxZj6RWZohpFjjxIEH3e5ftLwFLxK9i2PZujKLNaNtnl22gTg3F njd0OeNwRGBnHb4pN26Ddm/ktrF5dE+HzglHesI= X-Google-Smtp-Source: AGHT+IGwD7+0xkEodpOvmXlUBYMljj6vKGemgnZpWxivQFjYyap1Q+GKseRON87Px/UJBuDIZw3BWdH7Bq81Tzmv3/4= X-Received: by 2002:a05:6870:5d92:b0:1f9:6962:b06c with SMTP id fu18-20020a0568705d9200b001f96962b06cmr14296900oab.53.1701078306554; Mon, 27 Nov 2023 01:45:06 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Dominique Devienne Date: Mon, 27 Nov 2023 10:44:55 +0100 Message-ID: Subject: Re: Emitting JSON to file using COPY TO To: Adrian Klaver Cc: Davin Shearer , pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000003286d3060b1f287b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003286d3060b1f287b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Nov 27, 2023 at 10:33=E2=80=AFAM Dominique Devienne wrote: > On Sat, Nov 25, 2023 at 10:00=E2=80=AFPM Adrian Klaver > 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 COP= Y >> > 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 JSO= N >> > 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? --0000000000003286d3060b1f287b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Nov 27, 2023 at 10:33=E2=80=AFAM = Dominique Devienne <ddevienne@gma= il.com> wrote:
On Sat, = Nov 25, 2023 at 10:00=E2=80=AFPM Adrian Klaver <adrian.klaver@aklaver.com> wr= ote:
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. =C2=A0 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&qu= ot; 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?=C2= =A0
> Effectively, nn "as-is" option to COPY TO would work well fo= r 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<= /div>

> Any codepoint except " or \ or control c= haracters

And then is clearly shows \" as a v= alid 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&= #39;ve recently noticed=C2=A0= json_agg(row_to_json(t))<= /span>
is equivalent to=C2=A0json_agg(t)

Maybe use that inste= ad? Does that make a difference?

I haven't not= iced 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 configur= able. There's even a JSON mode.
By miracle, would the JSON ou= tput mode recognize JSON[B] values, and avoid the escaping?

<= /div>
--0000000000003286d3060b1f287b--