如何修复PostgreSQL jsonb列中的转义字符串为有效JSON

针对你的场景，用jsonb_set函数可以精准替换目标字段，不会破坏原有JSON结构，具体SQL如下：

UPDATE tbl1
SET info_data = jsonb_set(
    info_data,
    '{registrationDate}',
    (info_data->>'registrationDate')::jsonb
)
WHERE id = 1 
  AND info_data IS NOT NULL;

为什么这个方案有效 #

• info_data->>'registrationDate' 提取出registrationDate字段的字符串值（即带转义的JSON内容）
• ::jsonb 将该字符串直接解析为合法的JSON对象
• jsonb_set 仅替换info_data中的registrationDate字段，完整保留其他字段的原有结构

你之前的方法无效的原因 #

• 第一个语句(info_data #> '{}')::jsonb只是原样返回整个info_data，没有任何实际修改
• 第二个语句直接替换整个JSON文本中的转义符，会误改其他正常字段的双引号转义，甚至导致JSON解析错误

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