Re: JSON fields with backslashes

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.

David J.