json - Postgres row_to_json 生成带有双转义引号的无效 JSON

Question

Postgres 在创建 JSON 导出时错误地转义引号。请注意以下更新中的双引号...

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';

output.json的内容:

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

您可以看到引号未正确转义，并创建了无效的 JSON。应该是:

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

我该如何修复或解决这个 Postgres 错误？

Answer 1

这与 JSON 无关。这是关于 COPY 命令中文本格式(默认)处理反斜杠的方式。来自 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.

(强调我的。)
您可以通过使用 CSV 格式并将引号字符从双引号更改为其他内容来解决它。

演示:

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