MIME-Version: 1.0
References: <cb1f118c-4698-4451-9ba9-dd8595f06c37@joeconway.com>
 <926ff917-8371-40ec-b5e6-ab7b0e09bdc5@joeconway.com> <315b81d4-4b67-7828-0355-3808cd14acd1@dunslane.net>
 <7a60faf6-e7f1-419d-aee6-10a78ea2fe81@joeconway.com> <2e7ff718-895d-83fc-46f7-be25e23b23b4@dunslane.net>
 <!bbf922c5-f571-483e-a2c6-2730b02183fe@joeconway.com> <1104915.1701877459@sss.pgh.pa.us>
 <19a5f9d8-bd1f-9!e51-0f5b-510c1189a8a7@dunslane.net> <1110690.1701880139@sss.pgh.pa.us>
 <20231206163349.GA2873889@nathanxps13> <20231206164439.GC2873889@nathanxps13>
 <77312d02-9325-4f14-9fe0-2746c2ee12bc@joeconway.com> <1136975.1701894046@sss.pgh.pa.us>
 <8724f1d2-d11c-aaf2-2cd3-3725e9441d1e@dunslane.net>
In-Reply-To: <8724f1d2-d11c-aaf2-2cd3-3725e9441d1e@dunslane.net>
From: Sehrope Sarkuni <sehrope@jackdb.com>
Date: Wed, 6 Dec 2023 16:36:02 -0500
Message-ID: <CAH7T-arYm4FF2pPWDwz_6Fnp8Jj2vMNOfEd2B54HkFOPNX_kGw@mail.gmail.com>
Subject: Re: Emitting JSON to file using COPY TO
To: Andrew Dunstan <andrew@dunslane.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Joe Conway <mail@joeconway.com>, 
	Nathan Bossart <nathandbossart@gmail.com>, Davin Shearer <davin@apache.org>, 
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f35f9e060bde2376"
Archived-At: <https://www.postgresql.org/message-id/CAH7T-arYm4FF2pPWDwz_6Fnp8Jj2vMNOfEd2B54HkFOPNX_kGw%40mail.gmail.com>
Precedence: bulk

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

On Wed, Dec 6, 2023 at 4:03=E2=80=AFPM Andrew Dunstan <andrew@dunslane.net>=
 wrote:

> > The output size difference does say that maybe we should pay some
> > attention to the nearby request to not always label every field.
> > Perhaps there should be an option for each row to transform to
> > a JSON array rather than an object?
>
> I doubt it. People who want this are likely to want pretty much what
> this patch is providing, not something they would have to transform in
> order to get it. If they want space-efficient data they won't really be
> wanting JSON. Maybe they want Protocol Buffers or something in that vein.
>

For arrays v.s. objects, it's not just about data size. There are plenty of
situations where a JSON array is superior to an object (e.g. duplicate
column names). Lines of JSON arrays of strings is pretty much CSV with JSON
escaping rules and a pair of wrapping brackets. It's common for tabular
data in node.js environments as you don't need a separate CSV parser.

Each one has its place and a default of the row_to_json(...) representation
of the row still makes sense. But if the user has the option of outputting
a single json/jsonb field for each row without an object or array wrapper,
then it's possible to support all of these use cases as the user can
explicitly pick whatever envelope makes sense:

-- Lines of JSON arrays:
COPY (SELECT json_build_array('test-' || a, b) FROM generate_series(1, 3)
a, generate_series(5,6) b) TO STDOUT WITH (FORMAT JSON,
SOME_OPTION_TO_DISABLE_ENVELOPE);
["test-1", 5]
["test-2", 5]
["test-3", 5]
["test-1", 6]
["test-2", 6]
["test-3", 6]

-- Lines of JSON strings:
COPY (SELECT to_json('test-' || x) FROM generate_series(1, 5) x) TO STDOUT
WITH (FORMAT JSON, SOME_OPTION_TO_DISABLE_ENVELOPE);
"test-1"
"test-2"
"test-3"
"test-4"
"test-5"

I'm not sure how I feel about the behavior being automatic if it's a single
top level json / jsonb field rather than requiring the explicit option.
It's probably what a user would want but it also feels odd to change the
output wrapper automatically based on the fields in the response. If it is
automatic and the user wants the additional envelope, the option always
exists to wrap it further in another: json_build_object('some_field",
my_field_i_want_wrapped)

The duplicate field names would be a good test case too. I haven't gone
through this patch but I'm guessing it doesn't filter out duplicates so the
behavior would match up with row_to_json(...), i.e. duplicates are
preserved:

=3D> SELECT row_to_json(t.*) FROM (SELECT 1 AS a, 2 AS a) t;
  row_to_json
---------------
 {"a":1,"a":2}

If so, that's a good test case to add as however that's handled should be
deterministic.

Regards,
-- Sehrope Sarkuni
Founder & CEO | JackDB, Inc. | https://www.jackdb.com/

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

<div dir=3D"ltr"><div dir=3D"ltr"><div><div dir=3D"ltr" class=3D"gmail_sign=
ature"><div dir=3D"ltr"><div style=3D"font-family:arial;font-size:small"><s=
pan style=3D"font-family:Arial,Helvetica,sans-serif">On Wed, Dec 6, 2023 at=
 4:03=E2=80=AFPM Andrew Dunstan &lt;<a href=3D"mailto:andrew@dunslane.net">=
andrew@dunslane.net</a>&gt; wrote:</span><br></div></div></div></div></div>=
<div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margi=
n:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex=
">&gt; The output size difference does say that maybe we should pay some<br=
>
&gt; attention to the nearby request to not always label every field.<br>
&gt; Perhaps there should be an option for each row to transform to<br>
&gt; a JSON array rather than an object?<br><br>
I doubt it. People who want this are likely to want pretty much what <br>
this patch is providing, not something they would have to transform in <br>
order to get it. If they want space-efficient data they won&#39;t really be=
 <br>
wanting JSON. Maybe they want Protocol Buffers or something in that vein.<b=
r></blockquote><div><br></div>For arrays v.s. objects, it&#39;s not just ab=
out data size. There are plenty of situations where a JSON array is superio=
r to an object (e.g. duplicate column names). Lines of JSON arrays of strin=
gs is pretty much CSV with JSON escaping rules and a pair of wrapping brack=
ets. It&#39;s common for tabular data in node.js environments as you don=
9;t need a separate CSV parser.<br><br>Each one has its place and a default=
 of the row_to_json(...) representation of the row still makes sense. But i=
f the user has the option of outputting a single json/jsonb field for each =
row without an object or array wrapper, then it&#39;s possible to support a=
ll of these use cases as the user can explicitly pick whatever envelope mak=
es sense:<br><br>-- Lines of JSON arrays:<br>COPY (SELECT json_build_array(=
&#39;test-&#39; || a, b) FROM generate_series(1, 3) a, generate_series(5,6)=
 b) TO STDOUT WITH (FORMAT JSON, SOME_OPTION_TO_DISABLE_ENVELOPE);<br>[&quo=
t;test-1&quot;, 5]<br>[&quot;test-2&quot;, 5]<br>[&quot;test-3&quot;, 5]<br=
>[&quot;test-1&quot;, 6]<br>[&quot;test-2&quot;, 6]<br>[&quot;test-3&quot;,=
 6]<br><br>-- Lines of JSON strings:<br>COPY (SELECT to_json(&#39;test-&#39=
; || x) FROM generate_series(1, 5) x) TO STDOUT WITH (FORMAT JSON, SOME_OPT=
ION_TO_DISABLE_ENVELOPE);<br>&quot;test-1&quot;<br>&quot;test-2&quot;<br>&q=
uot;test-3&quot;<br>&quot;test-4&quot;<br>&quot;test-5&quot;<br><div><br></=
div><div>I&#39;m not sure how I feel about the behavior being automatic if =
it&#39;s a single top level json / jsonb field rather than requiring the ex=
plicit option. It&#39;s probably what a user would want but it also feels o=
dd to change the output wrapper automatically based on the fields in the re=
sponse. If it is automatic and the user wants the additional envelope, the =
option always exists to wrap it further in another: json_build_object(&#39;=
some_field&quot;, my_field_i_want_wrapped)</div><div><br></div><div>The dup=
licate field names would be a good test case too. I haven&#39;t gone throug=
h this patch but I&#39;m guessing it doesn&#39;t filter out duplicates so t=
he behavior would match up with row_to_json(...), i.e. duplicates are prese=
rved:</div><div><br></div><div>=3D&gt; SELECT row_to_json(t.*) FROM (SELECT=
 1 AS a, 2 AS a) t;<br>=C2=A0 row_to_json =C2=A0<br>---------------<br>=C2=
=A0{&quot;a&quot;:1,&quot;a&quot;:2}<br></div><div><span style=3D"font-fami=
ly:arial"><br></span></div><div><span style=3D"font-family:arial">If so, th=
at&#39;s a good test case to add as however that&#39;s handled should be de=
terministic.</span></div><div><span style=3D"font-family:arial"><br></span>=
</div><div><span style=3D"font-family:arial">Regards,</span><br></div><div =
style=3D"font-family:arial">-- Sehrope Sarkuni</div><div style=3D"font-fami=
ly:arial">Founder &amp; CEO | JackDB, Inc. |=C2=A0<a href=3D"https://www.ja=
ckdb.com/" target=3D"_blank">https://www.jackdb.com/</a></div><div><br></di=
v><div>=C2=A0</div></div></div>

--000000000000f35f9e060bde2376--