Thread: JSON fields with backslashes

Hello.  I have run into an issue when using the copy command on tables with json columns where Postgres seems to
improperlyescape 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? 

I am using PostgreSQL V12.16.

Thanks,
Greig Wise

Greig Wise <greigwise@comcast.net> writes:
> 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.

AFAICS this is COPY's normal behavior: it doubles backslashes in
the default format.  You could use CSV format, perhaps, but that
has its own quoting rules that also mean that what comes out is
not going to be json-and-nothing-but.  See "File Formats" here:

https://www.postgresql.org/docs/current/sql-copy.html

            regards, tom lane

On 2023-10-14 00:01 +0200, David G. Johnston write:
> On Fri, Oct 13, 2023 at 2:53 PM Greig Wise <greigwise@comcast.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