如何转义JSON值内双引号及PostgreSQL curl导入差异问题

现有存入PostgreSQL文本字段的非标准JSON存在未转义的内嵌双引号，直接转换为json类型会触发格式错误，测试代码如下：

create table test_txt (exif text);
insert into test_txt values ('{"id": "1234", "name": "this is "my" Name", "adress": "12345 City "of" test"}');
Select exif::json from test_txt; -- 执行报错：invalid input syntax for type json

同时需要确认两种curl拉取JSON数据的实现方式差异。

核心处理逻辑：仅对JSON值内部、不属于JSON语法分隔符的双引号添加转义符\，不得修改作为键名、键值边界的语法双引号。

方案1：SQL内正则替换（适配扁平结构JSON） #

如果你的JSON是无嵌套、无数组的扁平键值对结构，可以直接用正则替换完成修复：

SELECT
  regexp_replace(
    exif,
    '(:\s*"[^"]*)"([^"]*(?="[^,}]*"))',
    '\1\\"\2',
    'g'
  )::json AS valid_json
FROM test_txt;

执行后针对测试数据可以得到合法JSON：{"id": "1234", "name": "this is \"my\" Name", "adress": "12345 City \"of\" test"}

注意：正则方案对嵌套JSON、包含转义字符、数组的复杂JSON容错率极低，复杂场景不要用SQL做格式修复。

方案2：入库前预清洗（推荐） #

最稳妥的处理方式是在数据写入数据库前完成格式校验和修复：

• 拉取到JSON原始内容后，先用jq、Python脚本等JSON处理工具校验格式，批量替换值内的非法双引号
• 确认内容为合法JSON后再写入数据库，避免脏数据入库增加后续处理成本

两种方式拉取到的HTTP响应原始内容无本质区别，差异主要在执行逻辑、权限、可靠性层面：

• 权限要求不同
  • CMD控制台执行curl：使用当前登录系统的用户权限运行，只要当前用户能访问目标URL、有本地目录写入权限即可正常执行，无额外权限限制
  • PostgreSQL内COPY FROM PROGRAM调用curl：使用PostgreSQL服务进程的运行账号权限执行，不仅需要数据库超级用户权限才能开启program调用能力，还需要保证PostgreSQL服务账号有外网访问权限、curl命令执行权限，默认配置下大概率会权限报错
• 数据流转逻辑不同
  • 本地下载文件：curl拉取的内容会完整写入本地磁盘，导入数据库前可以手动打开文件校验完整性、修复格式问题，再选择合适的方式导入
  • 数据库内直接调用curl：curl返回的数据流直接通过COPY接口写入表字段，中间无落盘校验环节，网络波动导致的内容截断、源数据格式错误都会直接写入脏数据
• 异常排查难度不同
  • 本地执行curl：会直接返回HTTP状态码、连接错误、写入错误等明确提示，可以快速判断下载是否成功
  • 数据库内调用curl：curl的错误输出不会直接返回给SQL客户端，权限错误、网络错误很容易被误判为写入成功，问题排查成本更高

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