MIME-Version: 1.0
References: <CALvfUkBxTYy5uWPFVwpk_7ii2zgT07t3d-yR_cy4sfrrLU=kcg@mail.gmail.com>
 <fc1e39f8-274f-4be6-8a70-9fb0d9fe84a2@aklaver.com> <CAFCRh-_LhP5Ln4-beVz=1pQ9VycU3hAxnJHXhPHZRmX0BSCVEQ@mail.gmail.com>
 <CAFCRh--Vhx0105S7SWAYHGcVMTSRtbcoArDUGSbYSWRUGunW_g@mail.gmail.com>
 <CAKFQuwZb3zaSqkOvGXRRLZEW61az+4xyHZ7tBPR91JfrcdCKvA@mail.gmail.com> <3853387.1701096982@sss.pgh.pa.us>
In-Reply-To: <3853387.1701096982@sss.pgh.pa.us>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Mon, 27 Nov 2023 16:26:43 +0100
Message-ID: <CAFCRh-_GdiUvjd5z5FfvTfhruOnYqBu163XU47zZE8RNATCJGQ@mail.gmail.com>
Subject: Re: Emitting JSON to file using COPY TO
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, Adrian Klaver <adrian.klaver@aklaver.com>, 
	Davin Shearer <scholarsmate@gmail.com>, 
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000009b45e0060b23eee4"
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-_GdiUvjd5z5FfvTfhruOnYqBu163XU47zZE8RNATCJGQ%40mail.gmail.com>
Precedence: bulk

--0000000000009b45e0060b23eee4
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Nov 27, 2023 at 3:56=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > I agree there should be a copy option for =E2=80=9Cnot formatted=E2=80=
=9D so if you dump
> a
> > single column result in that format you get the raw unescaped contents =
of
> > the column.
>
> I'm not sure I even buy that.  JSON data in particular is typically
> multi-line, so how will you know where the row boundaries are?
> That is, is a newline a row separator or part of the data?
>
> You can debate the intelligence of any particular quoting/escaping
> scheme, but imagining that you can get away without having one at
> all will just create its own problems.
>

What I was suggesting is not about a "not formatted" option.
But rather than JSON values (i.e. typed `json` or `jsonb`) in a
JSON-formatted COPY operator, the JSON values should not be
serialized to text that is simply output as a JSON-text-value by COPY,
but "inlined" as a "real" JSON value without the JSON document output by
COPY.

This is a special case, where the inner and outer "values" (for lack of a
better terminology)
are *both* JSON documents, and given that JSON is hierarchical, the inner
JSON value can
either by 1) serializing to text first, which must thus be escaped using
the JSON escaping rules,
2) NOT serialized, but "inline" or "spliced-in" the outer COPY JSON
document.

I guess COPY in JSON mode supports only #1 now? While #2 makes more sense
to me.
But both options are valid. Is that clearer?

BTW, JSON is not multi-line, except for insignificant whitespace.
So even COPY in JSON mode is not supposed to be line based I guess?
Unless COPY in JSON mode is more like NDJSON (https://ndjson.org/)? --DD

--0000000000009b45e0060b23eee4
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Nov 27, 2023 at 3:56=E2=80=AFPM T=
om Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; =
wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote=
" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);=
padding-left:1ex">&quot;David G. Johnston&quot; &lt;<a href=3D"mailto:david=
.g.johnston@gmail.com" target=3D"_blank">david.g.johnston@gmail.com</a>&gt;=
 writes:<br>
&gt; I agree there should be a copy option for =E2=80=9Cnot formatted=E2=80=
=9D so if you dump a<br>
&gt; single column result in that format you get the raw unescaped contents=
 of<br>
&gt; the column.<br>
<br>
I&#39;m not sure I even buy that.=C2=A0 JSON data in particular is typicall=
y<br>
multi-line, so how will you know where the row boundaries are?<br>
That is, is a newline a row separator or part of the data?<br>
<br>
You can debate the intelligence of any particular quoting/escaping<br>
scheme, but imagining that you can get away without having one at<br>
all will just create its own problems.<br></blockquote><div><br></div><div>=
What I was suggesting is not about a &quot;not formatted&quot; option.</div=
><div>But rather than JSON values (i.e. typed `json` or `jsonb`) in a</div>=
<div>JSON-formatted COPY operator, the JSON values should not be</div><div>=
serialized to text that is simply output as a JSON-text-value by COPY,</div=
><div>but &quot;inlined&quot; as a &quot;real&quot; JSON value without the =
JSON document output by COPY.</div><div><br></div><div>This is a special ca=
se, where the inner and outer &quot;values&quot; (for lack of a better term=
inology)</div><div>are *both* JSON documents, and given that JSON is hierar=
chical, the inner JSON value can</div><div>either by 1) serializing to text=
 first, which must thus be escaped using the JSON escaping rules,</div><div=
>2) NOT serialized, but &quot;inline&quot; or &quot;spliced-in&quot; the ou=
ter COPY JSON document.</div><div><br></div><div>I guess COPY in JSON mode =
supports only #1 now? While #2 makes more sense to me.</div><div>But both o=
ptions are valid. Is that clearer?</div><div><br></div><div>BTW, JSON is no=
t multi-line, except for insignificant whitespace.</div><div>So even COPY i=
n JSON mode is not supposed to be line based I guess?</div><div>Unless COPY=
 in JSON mode is more like NDJSON (<a href=3D"https://ndjson.org/">https://=
ndjson.org/</a>)? --DD</div></div></div>

--0000000000009b45e0060b23eee4--