Re: Emitting JSON to file using COPY TO

Joe Conway <mail@joeconway.com>

From: Joe Conway <mail@joeconway.com>

To: Davin Shearer <davin@apache.org>, PostgreSQL-development <pgsql-hackers@postgreSQL.org>

Date: 2023-12-01T19:28:55Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Add option force_array for COPY JSON FORMAT
- 4c0390ac53b7 19 (unreleased) landed
json format for COPY TO
- 7dadd38cda95 19 (unreleased) landed
introduce CopyFormat, refactor CopyFormatOptions
- a2145605ee3d 19 (unreleased) landed
Doc: add IDs to copy.sgml's <varlistentry> and <refsect1>
- e4018f891dec 19 (unreleased) cited

Attachments

copyto_json.000.diff (text/x-patch) patch

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 AM Filip Sedlák <filip@sedlakovi.org 
> <mailto: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.

(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 commitfest.

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.

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com