PostgreSQL 15中更新JSON类型字段指定嵌套值的问题

PostgreSQL的json类型是不可变类型，不支持像jsonb那样直接通过字段路径赋值修改。你之前用的jdata['data']['variables']['dms-file-number'] = 'xxx'是jsonb专属的修改语法，所以对json列无效。

方法1：使用jsonb合并操作符|| #

通过将json转为jsonb完成修改，再转回json类型：

UPDATE "DOCF000T6"."assignments"
SET jdata = (jdata::jsonb || '{"data": {"variables": {"dms-file-number": "0001-05840"}}}'::jsonb)::json
WHERE jdata->'data'->'variables'->>'dms-file-number' = '0000-15840';

逻辑说明：

• jdata::jsonb：将原json列转为可修改的jsonb类型
• ||：jsonb的合并操作符，传入的嵌套JSON对象会覆盖原字段的对应值
• ::json：将修改后的jsonb转回json类型，赋值回原列

方法2：使用jsonb_set函数（更灵活） #

如果需要修改的字段路径更深，或者要动态指定路径，推荐用jsonb_set：

UPDATE "DOCF000T6"."assignments"
SET jdata = jsonb_set(
    jdata::jsonb,
    '{data, variables, dms-file-number}',
    '"0001-05840"'::jsonb
)::json
WHERE jdata->'data'->'variables'->>'dms-file-number' = '0000-15840';

参数说明：

1. 第一个参数：要修改的jsonb对象（原列转成的jsonb）
2. 第二个参数：字段路径数组，用大括号包裹层级键名
3. 第三个参数：新值，需转为jsonb类型（字符串要带双引号后转换）
4. 最后转回json类型赋值

长期优化建议 #

如果你的业务需要频繁修改JSON字段，建议直接将jdata列的类型改为jsonb：

ALTER TABLE "DOCF000T6"."assignments" ALTER COLUMN jdata TYPE jsonb USING jdata::jsonb;

修改后就可以直接用你最初尝试的语法进行更新，同时jsonb支持GIN索引，查询和修改的性能更优。

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