PostgreSQL解析JSON报invalid input syntax for type json错误如何修复

PostgreSQL的JSON/JSONB类型严格遵循JSON标准规范，要求所有键名和字符串值必须使用双引号包裹，你表中存在部分使用单引号包裹键值的非标准JSON数据，无法通过JSON类型校验，因此抛出语法错误。

分为两种场景处理：

场景1：临时查询，无需修改原表数据 #

在转换为JSONB前先将字段内的单引号全局替换为双引号即可，修改后的查询语句如下：

-- For multiple choice from JSON
SELECT
  s.projectid,
  s.prompttype,
  el.inputs->>'name' AS name,
  el.inputs->>'desc' AS desc,
  el.inputs->>'values' AS values,
  s.created,
  s.modified
FROM source_redshift.staticprompts AS s,
     jsonb_array_elements(replace(s.inputs, '''', '"')::jsonb) el(inputs);

注意：如果你的JSON字符串内容本身包含单引号（比如描述文本里的缩写、所有格表述），全局替换会破坏原有内容，这种情况需要先通过正则精准匹配JSON结构外层的单引号再替换，避免误改内容。

场景2：长期使用，从根源解决问题 #

建议直接修复表内的脏数据，同时将字段类型改为JSONB，避免后续再写入非法格式数据，操作步骤如下：

1. 提前备份全表数据，防止修改出错
2. 执行更新语句替换所有单引号为标准双引号：

UPDATE source_redshift.staticprompts
SET inputs = replace(inputs, '''', '"');

3. 修改字段类型为JSONB，开启写入格式校验：

ALTER TABLE source_redshift.staticprompts
ALTER COLUMN inputs TYPE JSONB USING inputs::JSONB;

修改完成后原来的查询语句就可以正常运行，不需要额外做替换处理。

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