MIME-Version: 1.0
References: <CALvfUkBxTYy5uWPFVwpk_7ii2zgT07t3d-yR_cy4sfrrLU=kcg@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>
 <CAH7T-aoWF9oHuUC2PJ303YVRFurkLMPayHawZayqdhtT2fpEHg@mail.gmail.com> <4d5688f4-9582-4093-8448-e1867bc9e2bc@joeconway.com>
In-Reply-To: <4d5688f4-9582-4093-8448-e1867bc9e2bc@joeconway.com>
From: Sehrope Sarkuni <sehrope@jackdb.com>
Date: Wed, 6 Dec 2023 16:42:11 -0500
Message-ID: <CAH7T-aow6jM-v1K462O7N=doOZxSD79zsH6prDCft7wv91F48A@mail.gmail.com>
Subject: Re: Emitting JSON to file using COPY TO
To: Joe Conway <mail@joeconway.com>
Cc: Andrew Dunstan <andrew@dunslane.net>, Tom Lane <tgl@sss.pgh.pa.us>, 
	Davin Shearer <davin@apache.org>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ed59aa060bde391c"
Archived-At: <https://www.postgresql.org/message-id/CAH7T-aow6jM-v1K462O7N%3DdoOZxSD79zsH6prDCft7wv91F48A%40mail.gmail.com>
Precedence: bulk

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

On Wed, Dec 6, 2023 at 4:29=E2=80=AFPM Joe Conway <mail@joeconway.com> wrot=
e:

> > 1. Outputting a top level JSON object without the additional column
> > keys. IIUC, the top level keys are always the column names. A common us=
e
> > 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 i=
t
> > 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}
>
> Your example does not match what you describe, or do I misunderstand? I
> thought your goal was to eliminate the repeated "foo" from each row...
>

The "foo" in this case is explicit as I'm adding it when building the
object. What I was trying to show was not adding an additional object
wrapper / envelope.

So each row is:

{"foo":1}

Rather than:

"{"json_build_object":{"foo":1}}

If each row has exactly one json / jsonb field, then the user has already
indicated the format for each row.

That same mechanism can be used to remove the "foo" entirely via a
json/jsonb array.

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

--000000000000ed59aa060bde391c
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>On Wed, Dec 6, 2023 at 4:29=E2=80=AFPM Joe Con=
way &lt;<a href=3D"mailto:mail@joeconway.com">mail@joeconway.com</a>&gt; wr=
ote:<br></div></div></div></div></div><div class=3D"gmail_quote"><blockquot=
e class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px s=
olid rgb(204,204,204);padding-left:1ex">&gt; 1. Outputting a top level JSON=
 object without the additional column <br>
&gt; keys. IIUC, the top level keys are always the column names. A common u=
se <br>
&gt; case would be a single json/jsonb column that is already formatted <br=
>
&gt; exactly as the user would like for output. Rather than enveloping it i=
n <br>
&gt; an object with a dedicated key, it would be nice to be able to output =
it <br>
&gt; directly. This would allow non-object results to be outputted as well =
<br>
&gt; (e.g., lines of JSON arrays, numbers, or strings). Due to how JSON is =
<br>
&gt; structured, I think this would play nice with the JSON lines v.s. arra=
y <br>
&gt; concept.<br>
&gt; <br>
&gt; COPY (SELECT json_build_object(&#39;foo&#39;, x) AS i_am_ignored FROM =
<br>
&gt; generate_series(1, 3) x) TO STDOUT WITH (FORMAT JSON, <br>
&gt; SOME_OPTION_TO_NOT_ENVELOPE)<br>
&gt; {&quot;foo&quot;:1}<br>
&gt; {&quot;foo&quot;:2}<br>
&gt; {&quot;foo&quot;:3}<br>
<br>
Your example does not match what you describe, or do I misunderstand? I <br=
>
thought your goal was to eliminate the repeated &quot;foo&quot; from each r=
ow...<br></blockquote><div><br></div><div><div>The &quot;foo&quot; in this =
case is explicit as I&#39;m adding it when building the object. What I was =
trying to show was not adding an additional object wrapper / envelope.</div=
><div><br></div><div>So each row is:</div><div><br></div><div>{&quot;foo&qu=
ot;:1}</div><div><br></div><div>Rather than:</div><div><br></div><div>&quot=
;{&quot;json_build_object&quot;:{&quot;foo&quot;:1}}</div><div><br></div><d=
iv>If each row has exactly one json / jsonb field, then the user has alread=
y indicated the format for each row.</div></div><div><br></div><div>That sa=
me mechanism can be used to remove the &quot;foo&quot; entirely via a json/=
jsonb array.</div><div style=3D"font-family:arial"><br class=3D"gmail-Apple=
-interchange-newline">Regards,</div><div style=3D"font-family:arial">-- Seh=
rope Sarkuni</div><div style=3D"font-family:arial">Founder &amp; CEO | Jack=
DB, Inc. |=C2=A0<a href=3D"https://www.jackdb.com/" target=3D"_blank">https=
://www.jackdb.com/</a></div><div><br></div><div>=C2=A0</div></div></div>

--000000000000ed59aa060bde391c--