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> <CAFCRh-_GdiUvjd5z5FfvTfhruOnYqBu163XU47zZE8RNATCJGQ@mail.gmail.com>
 <3a98decf-3fe3-4b49-9b68-fda01338872c@sedlakovi.org>
In-Reply-To: <3a98decf-3fe3-4b49-9b68-fda01338872c@sedlakovi.org>
From: Davin Shearer <davin@apache.org>
Date: Wed, 29 Nov 2023 10:32:54 -0500
Message-ID: <CALvfUkCq3pvkmniWEKZwkbA6hNNvkANF3OamMfhC170RgY+HJg@mail.gmail.com>
Subject: Re: Emitting JSON to file using COPY TO
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000005d0d50060b4c40d7"
Archived-At: <https://www.postgresql.org/message-id/CALvfUkCq3pvkmniWEKZwkbA6hNNvkANF3OamMfhC170RgY%2BHJg%40mail.gmail.com>
Precedence: bulk

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

Thanks for the responses everyone.

I worked around the issue using the `psql -tc` method as Filip described.

I think it would be great to support writing JSON using COPY TO at
some point so I can emit JSON to files using a PostgreSQL function directly=
.

-Davin

On Tue, Nov 28, 2023 at 2:36=E2=80=AFAM Filip Sedl=C3=A1k <filip@sedlakovi.=
org> wrote:

> This would be a very special case for COPY. It applies only to a single
> column of JSON values. The original problem can be solved with psql
> --tuples-only as David wrote earlier.
>
>
> $ psql -tc 'select json_agg(row_to_json(t))
>               from (select * from public.tbl_json_test) t;'
>
>   [{"id":1,"t_test":"here's a \"string\""}]
>
>
> Special-casing any encoding/escaping scheme leads to bugs and harder
> parsing.
>
> Just my 2c.
>
> --
> Filip Sedl=C3=A1k
>

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

<div dir=3D"ltr">Thanks for the responses everyone.<div><br></div><div>I wo=
rked around the issue using the `psql -tc` method as Filip described.</div>=
<div><br></div><div>I think it would be great to support writing JSON using=
 COPY TO at some=C2=A0point so=C2=A0I can emit JSON to=C2=A0files using a P=
ostgreSQL=C2=A0function directly.<div><br></div><div>-Davin</div></div></di=
v><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On T=
ue, Nov 28, 2023 at 2:36=E2=80=AFAM Filip Sedl=C3=A1k &lt;<a href=3D"mailto=
:filip@sedlakovi.org">filip@sedlakovi.org</a>&gt; wrote:<br></div><blockquo=
te class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left-widt=
h:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-le=
ft:1ex">This would be a very special case for COPY. It applies only to a si=
ngle <br>
column of JSON values. The original problem can be solved with psql <br>
--tuples-only as David wrote earlier.<br>
<br>
<br>
$ psql -tc &#39;select json_agg(row_to_json(t))<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 from (select * from public=
.tbl_json_test) t;&#39;<br>
<br>
=C2=A0 [{&quot;id&quot;:1,&quot;t_test&quot;:&quot;here&#39;s a \&quot;stri=
ng\&quot;&quot;}]<br>
<br>
<br>
Special-casing any encoding/escaping scheme leads to bugs and harder <br>
parsing.<br>
<br>
Just my 2c.<br>
<br>
--<br>
Filip Sedl=C3=A1k<br>
</blockquote></div>

--0000000000005d0d50060b4c40d7--