Re: Conflict between JSON_AGG and COPY

Đỗ Ngọc Trí Cường wrote:

> I want to export it to a file in JSON format so I run the query as below:
> COPY (SELECT...) TO '/home/postgres/test1.json'

COPY TO applies its own format rules on top of the data, which include
among other things, quoting the newline characters. What you seem
to expect is to copy a single-row, single-column piece of data verbatim
to the output file, but COPY does not offer that.
Such an option has already been discussed among developers, for
instance in this thread as a 'RAW' format:
https://www.postgresql.org/message-id/CAFj8pRAfer%2Bip3JCMTnvzra2QK7W9hk0hw1YqE8%2B%2BPZA1OqSJw%40mail.gmail.com
but eventually it was not implemented.

The unformatted output can be obtained client-side without COPY:
psql -Atc "SELECT row_to_json(...)" > /path/to/file

If you really need it server-side, a function that can write a result to
a file is required. I don't think it exists as such out of the box, but
you may either:

- write your own function in any untrusted language to
do just that (open file, write the piece of text into it, close it).

- or see pg_file_write() from the adminpack contrib module
https://www.postgresql.org/docs/current/static/adminpack.html
With that, you may call, as a superuser:
select pg_file_write('path/to/file.json',
(select row_to_json(...))::text,
false);
But note that the path must be relative to the PGDATA directory.

- or export the data as an intermediary large object at the cost of some
data churn in the large objects. And in that case, the path is not
constrained to postgres data directory.

do $$
declare
id oid;
j json;
begin
j := (select row_to_json(t) from <your query here>);
id := lo_from_bytea(0, convert_to(j::text, 'UTF-8'));
perform lo_export(id, '/path/to/file.json');
perform lo_unlink(id);
end;
$$ language plpgsql;

For all these server-side methods, you need to be superuser, just like for
COPY TO file anyway.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite