Geek Answers Handbook

Postgres row_to_json creates invalid JSON with double escaped quotes

Postgres incorrectly quotes when creating a JSON export. Note the double quotes in the next 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 quotation marks are hidden incorrectly and creates invalid JSON. It should be:

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

How can I fix this Postgres error or get around it?

+5
source share
2 answers

It is not related to JSON. This is about how the text format (by default) in the COPY handles the backslash. From the PostgreSQL documentation - COPY :

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

(My emphasis.)
You can solve it using the CSV format and changing the quote character from the double word 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\""} |
+7
source

Simo Kivistö's answer works if you are sure that the $ character or any other quotation mark character you have selected does not appear on your lines. In my case, I had to export a very large table, and there was no special character that did not appear in the rows.

To get around this problem, I passed the output of the COPY to sed to return double-escaped quotation marks:

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

I am expressing a sed expression that simply replaces the occurrences of \\" with \" .

+2
source

More articles:


All Articles