Re: JSON fields with backslashes

On 2023-10-14 00:01 +0200, David G. Johnston write:
> On Fri, Oct 13, 2023 at 2:53 PM Greig Wise <greigwise(at)comcast(dot)net> wrote:
>
> > Hello. I have run into an issue when using the copy command on tables
> > with json columns where Postgres seems to improperly escape backslashes
> > under certain conditions thus creating invalid JSON. Here is an example I
> > have of the behavior:
> >
> > create table test_json(json_data json);
> > insert into test_json values ('{"test1": "2011-01-01",
> > "description":"test\ntest2\ntest3 test''s\n \"Quoted Phrase\"
> > test\ntest."}’);
> > copy test_json to '/var/tmp/t.json’;
> >
> > cat /var/tmp/t.json
> > {"test1": "2011-01-01", "description":"test\\ntest2\\ntest3 test's\\n
> > \\"Quoted Phrase\\" test\\ntest."}
> >
> > Note that the quotes within the json field have \\ in front, thus negating
> > the escape of the quotes around “Quoted Phrase”. Which then renders the
> > whole thing invalid JSON. Is this a bug?
> >
>
> COPY doesn't output JSON, it outputs csv/tsv structured text. In that
> format the described output is correct. If you need a different output
> format you need to use a different tool. Ideally you can just get the JSON
> into whatever client software you are writing with and export it from
> there. Doing it in psql is possible but a bit tricky. Doing it within the
> server usually isn't worth the hassle.

in psql:

\pset format unaligned
\pset tuples_only
\o /var/tmp/t.json
select json_data from test_json limit 1;

--
Erik