Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r6yE2-003Oka-ND for pgsql-general@arkaria.postgresql.org; Sat, 25 Nov 2023 19:21:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r6yE1-004Mmx-0t for pgsql-general@arkaria.postgresql.org; Sat, 25 Nov 2023 19:21:57 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r6yE0-004Mlm-Eg for pgsql-general@lists.postgresql.org; Sat, 25 Nov 2023 19:21:56 +0000 Received: from mail-pl1-x62f.google.com ([2607:f8b0:4864:20::62f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1r6yDt-007KyS-9g for pgsql-general@lists.postgresql.org; Sat, 25 Nov 2023 19:21:55 +0000 Received: by mail-pl1-x62f.google.com with SMTP id d9443c01a7336-1ce627400f6so22476205ad.2 for ; Sat, 25 Nov 2023 11:21:49 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1700940108; x=1701544908; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=5WbLzeLayDkEhKHTK5jyCXUToghEB8Ti5zrJeIB119k=; b=FYXjH6qj/CQevfk/njItOJEP8GWi0puyAz6aXHmarXjGSP7nq60jij06ptWMSs7/8N fePz0Evn/rwgzewP5oh5YC/wCUB76d3UYen3fxTJ79N0w+pL8b1FxwEMC2vx2PPB9inJ XiXMTd0KXxTOaSOCx23hXDK0eHl0Uc0Gs3YGQylNKMII9x4HEzW+uToOkkbFfRreQEz8 ki25QTen28LJensgRGxENfkiJsgRsroAPeMdcQrxY2jN/8UNT/44QeGWcC8KtHSTuCwj 9/x4ziju343/YpxgdOXjDbcyXSD3/UTKbBny0M47oG916nqzE9Qn8Xr4E69zD8bY0UWj 9Usg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1700940108; x=1701544908; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=5WbLzeLayDkEhKHTK5jyCXUToghEB8Ti5zrJeIB119k=; b=FfBZWTN/+enTJXvGzWExd3qUsg2AweklRrUZKLRAJn9D4HZHHzyAtrqn72yB4MHzXX d40pSyLxyVPAMm2B2TziF1ZBwtowQo7z4EX433EIRGUZqygKgk0GjATSTR7H9RTCT4ie Y21N3Su7oUdq4AjWGrgMyyC2e7Nno65ZiBYT5VdSc/oEm8j7pOpDiL40nVNlxmhUElJI TkZrgkKcKnmC8Gf6EJqGG3sj/CzxCZaLHQiCiJ8LG/LL1zb9zMfTcjkDAlmFW1Cz2ik6 tS61SJAU6/JW0oGSoHLvEX+8tb3Nwo0+M2vdwM3Ckko7EyRp1BJX6UeK4c1Q7R80YsQG Ep1A== X-Gm-Message-State: AOJu0YzOKymavxsCv0J5Ea9Ri68XbIiU/zBps/XLvO4YftluT2DSjv0t oxvI1JQ69Bvoxv9eB0m0Rm6NYfE+MOlEEtJwij3Cm/YZf9xFWA== X-Google-Smtp-Source: AGHT+IEC7fGJBTzfR9SR3ZosinK7l91exEgWe6uuMy/jqCn15Y4q3F91TOYzJ0DGi+VOpEwL6kmDQyY8uk9QUO6ngKw= X-Received: by 2002:a17:90b:3504:b0:285:3511:6269 with SMTP id ls4-20020a17090b350400b0028535116269mr6963946pjb.38.1700940107766; Sat, 25 Nov 2023 11:21:47 -0800 (PST) MIME-Version: 1.0 From: Davin Shearer Date: Sat, 25 Nov 2023 14:21:37 -0500 Message-ID: Subject: Emitting JSON to file using COPY TO To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000e861ba060afefac1" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e861ba060afefac1 Content-Type: text/plain; charset="UTF-8" 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 --000000000000e861ba060afefac1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello!

I'm trying to emit a JSON ag= gregation of JSON rows to a file using COPY TO, but I'm running into pr= oblems with COPY TO double quoting the output. =C2=A0 Here is a minimal exa= mple that demonstrates the problem I'm having:

=
create table public.tbl_json_=
test (id int, t_tes=
t 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 ro=
ws
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 '/tm=
p/tbl_json_test.json';
-- once the JSON results are copied to file, the JSON is broken due to dou=
ble quoting:
-- [{"id=
":1,"t_test":"here's a \\"string\\""=
}]
-- this fails to be par=
sed 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 tex= t field in a table with text containing at least one double-quote (").= =C2=A0 We then select from that table, formating the result as a JSON aggre= gation of JSON rows.=C2=A0 At this point the JSON syntax is correct,=C2=A0w= ith the double quotes being properly quoted.=C2=A0 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 str= ing (''), but they only apply to the CSV format.

Is there a way to emit JSON results to file from within postgres?=C2= =A0 Effectively, nn "as-is" option to COPY TO would work well for= this JSON use case.

Any assistance=C2=A0would be = appreciated.

Thanks,
Davin
--000000000000e861ba060afefac1--