Search code examples
jsonpostgresql

Postgres row_to_json produces invalid JSON with double escaped quotes

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?

Solution

  • 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\""} |