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:
create table public.tbl_json_test (id int, t_test text);
-- insert text that includes double quotes
insert into public.tbl_json_test (id, t_test) values (1, 'here''s a "string"');
-- select a JSON aggregation of JSON rows
select json_agg(row_to_json(t)) from (select * from public.tbl_json_test) t;
-- this yields the correct result in proper JSON format:
-- [{"id":1,"t_test":"here's a \"string\""}]
copy (select json_agg(row_to_json(t)) from (select * from public.tbl_json_test) t) to '/tmp/tbl_json_test.json';
-- once the JSON results are copied to file, the JSON is broken due to double quoting:
-- [{"id":1,"t_test":"here's a \\"string\\""}]
-- this fails to be parsed using jq on the command line:
-- cat /tmp/tbl_json_test.json | jq .
-- jq: parse error: Invalid numeric literal at line 1, column 40
We populate a text field in a table with text containing at least one double-quote ("). We then select from that table, formating the result as a JSON aggregation of JSON rows. At this point the JSON syntax is correct, with the double quotes being properly quoted. The problem is that once we use COPY TO to emit the results to a file, the output gets quoted again with a second escape character (\), breaking the JSON and causing a syntax error (as we can see above using the `jq` command line tool).
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.
Any assistance would be appreciated.
Thanks,
Davin