json - 将 JSON 插入到包含引号的 PostgreSQL 中

Question

我正在尝试将 JSON 文件导入表中。我正在使用此处提到的解决方案:https://stackoverflow.com/a/33130304/1663462 :

create temporary table temp_json (values text) on commit drop;
copy temp_json from 'data.json';

select
  values->>'annotations' as annotationstext

from   (
           select json_array_elements(replace(values,'\','\\')::json) as values
           from   temp_json
       ) a;

Json文件内容为:

{"annotations": "<?xml version=\"1.0\"?>"}

我已验证这是一个有效的 JSON 文件。

json 文件包含一个 \"，我认为这是导致以下错误的原因:

CREATE TABLE
COPY 1
psql:insertJson2.sql:13: ERROR:  invalid input syntax for type json
DETAIL:  Expected "," or "}", but found "1.0".
CONTEXT:  JSON data, line 1: {"annotations": "<?xml version="1.0...

是否还有其他需要转义的字符？

Answer 1

因为 copy 命令处理文本格式的转义 ('\') 字符而没有任何选项，所以有两种方法可以导入此类数据。

1) 通过copy ... from program 使用外部工具处理文件，例如使用sed:

copy temp_json from program 'sed -e ''s/\\/\\\\/g'' data.json';

它将所有反斜杠替换为双反斜杠，通过copy将其转换回单个反斜杠。

2) 使用csv导入:

copy temp_json from 'data.json' with (format csv, quote '|', delimiter E'\t');

在这里您应该设置quote 和delimiter 字符，这样它就不会出现在您的文件中的任何地方。

然后直接使用直接转换:

select values::json->>'annotations' as annotationstext from temp_json;