Postgres row_to_json produces invalid JSON with double escaped quotes

Question

Postgres escapes quotes incorrectly when creating a JSON export. Note the double quotes in the below update...

UPDATE models SET column='"hello"' WHERE id=1;

COPY (SELECT row_to_json(models)
    FROM (SELECT column FROM shaders WHERE id=1) shaders)
    TO '/output.json';

The contents of output.json:

{"column":"\\"hello\\""}

You can see that the quotes are escaped improperly and it creates invalid JSON. It should be:

{"column":"\"hello\""}

How can I fix this Postgres bug or work around it?

Answer 1

This is not JSON related. It's about the way text format (default) in COPY command handles backslashes. From the PostgreSQL documentation - COPY:

Backslash characters (\) can be used in the COPY data to quote data characters that might otherwise be taken as row or column delimiters. In particular, the following characters must be preceded by a backslash if they appear as part of a column value: backslash itself, newline, carriage return, and the current delimiter character.

(Emphasis mine.)
You can solve it by using CSV-format and changing the quote character from doublequote to something else.

To demonstrate:

SELECT row_to_json(row('"hello"'))
 | "{"f1":"\"hello\""}" |

COPY (SELECT row_to_json(row('"hello"'))) TO '/output.json';
 | {"f1":"\\"hello\\""} |

COPY (SELECT row_to_json(row('"hello"'))) TO '/output.json' CSV QUOTE '$';
 | {"f1":"\"hello\""} |

Answer 2

The answer by Simo Kivistö works if you are certain that the character $, or whatever the special quote character you chose does not appear in your strings. In my case, I had to export a very large table and there was no particular character which didn't appear in the strings.

To work around this issue, I piped the output of the COPY command to sed to revert the double escaping of quotes:

psql -c "COPY (SELECT row_to_json(t) from my_table as t) to STDOUT;" |
  sed 's/\\"/\"/g' > my_table.json

The sed expression I am piping to simply replaces occurrences of \\" with \".