Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r9oTM-002RxZ-71 for pgsql-hackers@arkaria.postgresql.org; Sun, 03 Dec 2023 15:33:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r9oTJ-00CobE-PW for pgsql-hackers@arkaria.postgresql.org; Sun, 03 Dec 2023 15:33:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r9oTJ-00Cob5-Eh for pgsql-hackers@lists.postgresql.org; Sun, 03 Dec 2023 15:33:29 +0000 Received: from mxout1-ec2-va.apache.org ([3.227.148.255]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r9oTG-009u1Z-1A for pgsql-hackers@postgresql.org; Sun, 03 Dec 2023 15:33:28 +0000 Received: from mail.apache.org (mailgw-he-de.apache.org [116.203.246.181]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) (No client certificate requested) by mxout1-ec2-va.apache.org (ASF Mail Server at mxout1-ec2-va.apache.org) with ESMTPS id 29D924C54C for ; Sun, 3 Dec 2023 15:32:13 +0000 (UTC) Received: (qmail 435845 invoked by uid 116); 3 Dec 2023 15:32:12 -0000 Received: from ec2-52-204-25-47.compute-1.amazonaws.com (HELO mailrelay1-ec2-va.apache.org) (52.204.25.47) by apache.org (qpsmtpd/0.94) with ESMTP; Sun, 03 Dec 2023 15:32:12 +0000 Authentication-Results: apache.org; auth=none Received: from mail-pg1-f181.google.com (mail-pg1-f181.google.com [209.85.215.181]) by mailrelay1-ec2-va.apache.org (ASF Mail Server at mailrelay1-ec2-va.apache.org) with ESMTPSA id 62E5E4022D for ; Sun, 3 Dec 2023 15:32:10 +0000 (UTC) Received: by mail-pg1-f181.google.com with SMTP id 41be03b00d2f7-5c673b01eeeso101747a12.1 for ; Sun, 03 Dec 2023 07:32:10 -0800 (PST) X-Gm-Message-State: AOJu0YxR68i7WBzKf46WBh6tMfW4rVoU00kHewUC4Jq4mUzCDgD0gtj8 jImJjS9QswPDgEFrgyCmelLq4O8jceHK0GeQVsE= X-Google-Smtp-Source: AGHT+IFI6XhchYZRz3/HyLQgzzhPmBRXplu5F1Jpubm5wh5XCZmK2SA4MnuKOD0DlKuvgzJztmbDG/10+W8tZnl9E3I= X-Received: by 2002:a17:90a:6e48:b0:286:6cc1:5fbf with SMTP id s8-20020a17090a6e4800b002866cc15fbfmr662574pjm.66.1701617529437; Sun, 03 Dec 2023 07:32:09 -0800 (PST) MIME-Version: 1.0 References: <3853387.1701096982@sss.pgh.pa.us> <3a98decf-3fe3-4b49-9b68-fda01338872c@sedlakovi.org> <24e3ee88-ec1e-421b-89ae-8a47ee0d2df1@joeconway.com> In-Reply-To: From: Davin Shearer Date: Sun, 3 Dec 2023 10:31:58 -0500 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: Emitting JSON to file using COPY TO To: PostgreSQL-development , Joe Conway Cc: Andrew Dunstan Content-Type: multipart/alternative; boundary="00000000000062ca39060b9cb489" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000062ca39060b9cb489 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Please be sure to include single and double quotes in the test values since that was the original problem (double quoting in COPY TO breaking the JSON syntax). On Sun, Dec 3, 2023, 10:11 Andrew Dunstan wrote: > > On 2023-12-01 Fr 14:28, Joe Conway wrote: > > On 11/29/23 10:32, Davin Shearer wrote: > >> 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 >> > 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 psq= l > >> --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 hard= er > >> parsing. > > > > (moved to hackers) > > > > I did a quick PoC patch (attached) -- if there interest and no hard > > objections I would like to get it up to speed for the January commitfes= t. > > > > Currently the patch lacks documentation and regression test support. > > > > Questions: > > ---------- > > 1. Is supporting JSON array format sufficient, or does it need to > > support some other options? How flexible does the support scheme need > > to be? > > > > 2. This only supports COPY TO and we would undoubtedly want to support > > COPY FROM for JSON as well, but is that required from the start? > > > > Thanks for any feedback. > > > I realize this is just a POC, but I'd prefer to see composite_to_json() > not exposed. You could use the already public datum_to_json() instead, > passing JSONTYPE_COMPOSITE and F_RECORD_OUT as the second and third > arguments. > > I think JSON array format is sufficient. > > I can see both sides of the COPY FROM argument, but I think insisting on > that makes this less doable for release 17. On balance I would stick to > COPY TO for now. > > > cheers > > > andrew > > > -- > Andrew Dunstan > EDB: https://www.enterprisedb.com > > --00000000000062ca39060b9cb489 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Please be sure to include single and double quotes in the= test values since that was the original problem (double quoting in COPY TO= breaking the JSON syntax).

On Sun, Dec 3, 2023, 10:11 Andrew Dunstan <andrew@dunslane.net> wrote:
=

On 2023-12-01 Fr 14:28, Joe Conway wrote:
> On 11/29/23 10:32, Davin Shearer wrote:
>> 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=C2=A0point so=C2=A0I can emit JSON to=C2=A0files using a Post= greSQL=C2=A0function
>> directly.
>>
>> -Davin
>>
>> On Tue, Nov 28, 2023 at 2:36=E2=80=AFAM Filip Sedl=C3=A1k <fil= ip@sedlakovi.org
>> <mailto:filip@sedlakovi.org>> wrote:
>>
>> =C2=A0=C2=A0=C2=A0 This would be a very special case for COPY. It = applies only to a
>> single
>> =C2=A0=C2=A0=C2=A0 column of JSON values. The original problem can= be solved with psql
>> =C2=A0=C2=A0=C2=A0 --tuples-only as David wrote earlier.
>>
>>
>> =C2=A0=C2=A0=C2=A0 $ psql -tc 'select json_agg(row_to_json(t))=
>> =C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 from (select * from public.tbl_json_test) t;'
>>
>> =C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 [{"id":1,"t_test&qu= ot;:"here's a \"string\""}]
>>
>>
>> =C2=A0=C2=A0=C2=A0 Special-casing any encoding/escaping scheme lea= ds to bugs and harder
>> =C2=A0=C2=A0=C2=A0 parsing.
>
> (moved to hackers)
>
> I did a quick PoC patch (attached) -- if there interest and no hard > objections I would like to get it up to speed for the January commitfe= st.
>
> Currently the patch lacks documentation and regression test support. >
> Questions:
> ----------
> 1. Is supporting JSON array format sufficient, or does it need to
> support some other options? How flexible does the support scheme need =
> to be?
>
> 2. This only supports COPY TO and we would undoubtedly want to support=
> COPY FROM for JSON as well, but is that required from the start?
>
> Thanks for any feedback.


I=C2=A0 realize this is just a POC, but I'd prefer to see composite_to_= json()
not exposed. You could use the already public datum_to_json() instead,
passing JSONTYPE_COMPOSITE and F_RECORD_OUT as the second and third
arguments.

I think JSON array format is sufficient.

I can see both sides of the COPY FROM argument, but I think insisting on that makes this less doable for release 17. On balance I would stick to COPY TO for now.


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com

--00000000000062ca39060b9cb489--