PostgreSQL中JSON转TEXT遇错误及替换失败求助

问题原因 #

PostgreSQL的JSON类型不允许包含ASCII NULL字符（�），当用->>提取字段时，数据库解析到这个字符就会抛出22P05错误。直接用regexp_replace处理JSON列报错，是因为该函数仅接受text类型参数，JSON类型无法直接传入。

解决方案 #

以下几种方法可以解决这个问题：

方法1：通过bytea中转清理NULL字符后提取

先将JSON列转换为bytea再转回text，此时NULL字符会被转义为\u0000，替换后再转回JSON类型提取字段：

SELECT 
  (regexp_replace(json_data::bytea::text, '\\u0000', '', 'g')::json)->>'text' AS text
FROM table1;

方法2：直接转换JSON为text清理后提取

跳过bytea中转，直接将JSON转为text，替换NULL转义序列后再解析为JSON提取字段：

SELECT 
  json_extract_path_text(regexp_replace(json_data::text, '\\u0000', '', 'g')::json, 'text') AS text
FROM table1;

方法3：使用jsonb处理（PostgreSQL 9.4+）

如果你的PostgreSQL版本支持jsonb，可以用同样的清理逻辑转为jsonb后提取：

SELECT 
  (regexp_replace(json_data::text, '\\u0000', '', 'g')::jsonb)->>'text' AS text
FROM table1;

批量清理表数据（可选） #

如果希望一劳永逸，避免每次查询都处理，可以直接更新表中的JSON数据，清理掉NULL字符：

UPDATE table1
SET json_data = regexp_replace(json_data::text, '\\u0000', '', 'g')::json
WHERE json_data::text LIKE '%\\u0000%';

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