MIME-Version: 1.0
References: <CALvfUkBxTYy5uWPFVwpk_7ii2zgT07t3d-yR_cy4sfrrLU=kcg@mail.gmail.com>
 <CALvfUkB2QtG-BXsTwjoLO8ZYUsNFytqjPvZex8stQ0r_qNM7HQ@mail.gmail.com>
 <5c84b70b-ba18-c45d-dbbe-612fa229b2ce@dunslane.net> <CALvfUkDxgTppjy-Q_i2znakMFp2vrJdPUEJNyGTL2LcZBOWV6A@mail.gmail.com>
 <cbd65026-d79c-40b1-8be3-10e1db2fc092@joeconway.com> <398c22f6-4299-4b17-80bf-2f14f4afd592@joeconway.com>
 <CALvfUkAbj4Bwg0FjYpvj0-zddcetWg1Sejt=OGBTDskbqOFS_A@mail.gmail.com>
 <46cc4507-a0d9-4044-b2ce-5a8bca8015c0@joeconway.com> <CALvfUkD8+bcNs1cYeU3wK9YsyZ7zUjs2aTxO+++Vq7zKeUE7+w@mail.gmail.com>
 <2554e520-e103-8978-dcb5-807dfeb77402@dunslane.net> <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>
 <1104915.1701877459@sss.pgh.pa.us> <19a5f9d8-bd1f-9e51-0f5b-510c1189a8a7@dunslane.net>
In-Reply-To: <19a5f9d8-bd1f-9e51-0f5b-510c1189a8a7@dunslane.net>
From: Sehrope Sarkuni <sehrope@jackdb.com>
Date: Wed, 6 Dec 2023 11:28:33 -0500
Message-ID: <CAH7T-aoWF9oHuUC2PJ303YVRFurkLMPayHawZayqdhtT2fpEHg@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>, 
	Davin Shearer <davin@apache.org>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000004a5da8060bd9d88f"
Archived-At: <https://www.postgresql.org/message-id/CAH7T-aoWF9oHuUC2PJ303YVRFurkLMPayHawZayqdhtT2fpEHg%40mail.gmail.com>
Precedence: bulk

--0000000000004a5da8060bd9d88f
Content-Type: text/plain; charset="UTF-8"

Big +1 to this overall feature.

This is something I've wanted for a long time as well. While it's possible
to use a COPY with text output for a trivial case, the double escaping
falls apart quickly for arbitrary data. It's really only usable when you
know exactly what you are querying and know it will not be a problem.

Regarding the defaults for the output, I think JSON lines (rather than a
JSON array of objects) would be preferred. It's more natural to combine
them and generate that type of data on the fly rather than forcing
aggregation into a single object.

Couple more features / use cases come to mind as well. Even if they're not
part of a first round of this feature I think it'd be helpful to document
them now as it might give some ideas for what does make that first cut:

1. Outputting a top level JSON object without the additional column keys.
IIUC, the top level keys are always the column names. A common use case
would be a single json/jsonb column that is already formatted exactly as
the user would like for output. Rather than enveloping it in an object with
a dedicated key, it would be nice to be able to output it directly. This
would allow non-object results to be outputted as well (e.g., lines of JSON
arrays, numbers, or strings). Due to how JSON is structured, I think this
would play nice with the JSON lines v.s. array concept.

COPY (SELECT json_build_object('foo', x) AS i_am_ignored FROM
generate_series(1, 3) x) TO STDOUT WITH (FORMAT JSON,
SOME_OPTION_TO_NOT_ENVELOPE)
{"foo":1}
{"foo":2}
{"foo":3}

2. An option to ignore null fields so they are excluded from the output.
This would not be a default but would allow shrinking the total size of the
output data in many situations. This would be recursive to allow nested
objects to be shrunk down (not just the top level). This might be
worthwhile as a standalone JSON function though handling it during output
would be more efficient as it'd only be read once.

COPY (SELECT json_build_object('foo', CASE WHEN x > 1 THEN x END) FROM
generate_series(1, 3) x) TO STDOUT WITH (FORMAT JSON,
SOME_OPTION_TO_NOT_ENVELOPE, JSON_SKIP_NULLS)
{}
{"foo":2}
{"foo":3}

3. Reverse of #2 when copying data in to allow defaulting missing fields to
NULL.

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

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

<div dir=3D"ltr"><div dir=3D"ltr">Big +1 to this overall feature.<br><br>Th=
is is something I&#39;ve wanted for a long time as well. While it&#39;s pos=
sible to use a COPY with text output for a trivial case, the double escapin=
g falls apart quickly for arbitrary data. It&#39;s=C2=A0really only usable =
when you know exactly what you are querying and know it will not be a probl=
em.<br><br>Regarding the defaults for the output, I think JSON lines (rathe=
r than a JSON array of objects) would be preferred. It&#39;s more natural t=
o combine them and generate that type of data on the fly rather than forcin=
g aggregation into a single object.<br><br>Couple more features / use cases=
 come to mind as well. Even if they&#39;re not part of a first round of thi=
s feature I think it&#39;d be helpful to document them now as it might give=
 some ideas for what does make that first cut:<br><br>1. Outputting a top l=
evel JSON object without the additional column keys. IIUC, the top level ke=
ys are always the column names. A common use case would be a single json/js=
onb column that is already formatted exactly as the user would like for out=
put. Rather than enveloping it in an object with a dedicated key, it would =
be nice to be able to output it directly. This would allow non-object resul=
ts to be outputted as well (e.g., lines of JSON arrays, numbers, or strings=
). Due to how JSON is structured, I think this would play nice with the JSO=
N lines v.s. array concept.<br><br>COPY (SELECT json_build_object(&#39;foo&=
#39;, x) AS i_am_ignored FROM generate_series(1, 3) x) TO STDOUT WITH (FORM=
AT JSON, SOME_OPTION_TO_NOT_ENVELOPE)<br>{&quot;foo&quot;:1}<br>{&quot;foo&=
quot;:2}<br>{&quot;foo&quot;:3}<br><br>2. An option to ignore null fields s=
o they are excluded from the output. This would not be a default but would =
allow shrinking the total size of the output data in many situations. This =
would be recursive to allow nested objects to be shrunk down (not just the =
top level). This might be worthwhile as a standalone JSON function though h=
andling it during output would be more efficient as it&#39;d only be read o=
nce.<br><br>COPY (SELECT json_build_object(&#39;foo&#39;, CASE WHEN x &gt; =
1 THEN x END) FROM generate_series(1, 3) x) TO STDOUT WITH (FORMAT JSON, SO=
ME_OPTION_TO_NOT_ENVELOPE, JSON_SKIP_NULLS)<br>{}<br>{&quot;foo&quot;:2}<br=
>{&quot;foo&quot;:3}<br><br>3. Reverse of #2 when copying data in to allow =
defaulting missing fields to NULL.<br></div><div dir=3D"ltr"><br clear=3D"a=
ll"><div><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div s=
tyle=3D"font-family:arial;font-size:small">Regards,</div><div style=3D"font=
-family:arial;font-size:small">-- Sehrope Sarkuni</div><div style=3D"font-f=
amily:arial;font-size:small">Founder &amp; CEO | JackDB, Inc. |=C2=A0<a hre=
f=3D"https://www.jackdb.com/" style=3D"color:rgb(17,85,204)" target=3D"_bla=
nk">https://www.jackdb.com/</a></div></div></div></div></div></div>

--0000000000004a5da8060bd9d88f--