Re: JSON fields with backslashes - Mailing list pgsql-general

From Erik Wienhold
Subject Re: JSON fields with backslashes
Date
Msg-id 4r4tuewwy4eznabmmcdxnmf5ddk753wtdp6edgcvh5ealspd5z@ldcf5pyzsidi
Whole thread Raw
In response to Re: JSON fields with backslashes  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-general
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



pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: JSON fields with backslashes
Next
From: Stefan Mayr
Date:
Subject: RPM-Sync complains about wrong checksums for SLES12 repo