public inbox for [email protected]  

help / color / mirror / Atom feed

From: Davin Shearer <[email protected]>
To: [email protected]
Subject: Emitting JSON to file using COPY TO
Date: Sat, 25 Nov 2023 14:21:37 -0500
Message-ID: <CALvfUkBxTYy5uWPFVwpk_7ii2zgT07t3d-yR_cy4sfrrLU=kcg@mail.gmail.com> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Emitting JSON to file using COPY TO
  In-Reply-To: <CALvfUkBxTYy5uWPFVwpk_7ii2zgT07t3d-yR_cy4sfrrLU=kcg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox