Re: Emitting JSON to file using COPY TO

Adrian Klaver <adrian.klaver@aklaver.com>

From: Adrian Klaver <adrian.klaver@aklaver.com>

To: Davin Shearer <scholarsmate@gmail.com>, pgsql-general@lists.postgresql.org

Date: 2023-11-25T21:00:12Z

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

On 11/25/23 11:21, Davin Shearer wrote:
> Hello!
> 
> I'm trying to emit a JSON aggregation of JSON rows to a file using COPY 
> TO, but I'm running into problems with COPY TO double quoting the 
> output.   Here is a minimal example that demonstrates the problem I'm 
> having:
> 

> I have tried to get COPY TO to copy the results to file "as-is" by 
> setting the escape and the quote characters to the empty string (''), 
> but they only apply to the CSV format.
> 
> Is there a way to emit JSON results to file from within postgres?  
> Effectively, nn "as-is" option to COPY TO would work well for this JSON 
> use case.
> 

Not using COPY.

See David Johnson's post for one way using the client psql.

Otherwise you will need to use any of the many ETL programs out there 
that are designed for this sort of thing.

> Any assistance would be appreciated.
> 
> Thanks,
> Davin

-- 
Adrian Klaver
adrian.klaver@aklaver.com