PostgreSQL如何正确导出jsonb类型列？

错误原因 #

你当前的写法导出的内容不符合标准JSON格式：

• 默认COPY TO的文本格式会自动转义jsonb内的双引号、换行等特殊字符，和JSON本身的转义规则冲突
• 导出的每一行是单独的JSON对象，但整个文件既不是合法的JSON数组，也不是标准的JSON Lines格式，所以jq和Python直接读取整个文件会解析失败

正确导出方法 #

场景1：导出为JSON Lines格式（每行一个独立JSON对象，适合逐行处理） #

这种格式jq和Python都可以直接逐行读取解析，性能更高，适合大数据量：

COPY (SELECT data::text FROM your_table) TO '/tmp/data.jsonl' WITH (
  FORMAT text,
  ENCODING 'UTF8',
  ESCAPE 'off'
);

如果没有服务器文件写入权限，可以用psql客户端的\copy命令，结果存在本地：

\copy (SELECT data::text FROM your_table) TO '/tmp/data.jsonl' WITH (FORMAT text, ENCODING 'UTF8', ESCAPE 'off')

读取方式：

• jq直接读：jq . /tmp/data.jsonl
• Python逐行读：

import json
res = []
with open('/tmp/data.jsonl', 'r', encoding='utf8') as f:
    for line in f:
        res.append(json.loads(line.strip()))

场景2：导出为单文件合法JSON数组（所有结果放在一个JSON数组里，适合小数据量） #

COPY (SELECT json_agg(data)::text FROM your_table) TO '/tmp/data.json' WITH (
  FORMAT csv,
  QUOTE e'\x01',
  DELIMITER e'\x02',
  HEADER off
);

这里用了不可见字符作为分隔符和Quote字符，完全避免PostgreSQL对JSON内容的转义，导出的文件就是标准的JSON数组，直接用jq和Python加载即可：

• jq读：jq . /tmp/data.json
• Python读：

import json
with open('/tmp/data.json', 'r', encoding='utf8') as f:
    res = json.load(f)

内容的提问来源于stack exchange，提问作者browse