PostgreSQL如何单次更新JSONB对象内多个嵌套值且不改变原有结构
PostgreSQL JSONB 嵌套字段批量更新方案
你之前尝试的两种方案各自存在明确的缺陷:
- 嵌套
jsonb_set:更新字段多的时候嵌套层级过深,可读性和可维护性极差 - 直接用
||拼接:JSONB的||是浅合并,只会覆盖顶层键,会丢失嵌套层级下未指定的字段
以下是三种可落地的解决方案,覆盖不同版本和使用场景:
方案1:链式jsonb_set(兼容所有支持JSONB的PostgreSQL版本)
不需要嵌套,按顺序对齐书写即可,适合字段数不多的场景:
UPDATE rating SET food_value = jsonb_set( jsonb_set( food_value, '{food, type, name}', '"not bad"'::jsonb ), '{food, category, id}', '1'::jsonb -- 数字类型无需额外加引号,避免类型错误 ) WHERE 1=1 -- 补充你的筛选条件 ;
方案2:迭代批量更新(适合需要更新几十上百个字段的场景)
把所有需要更新的路径和值整理为值列表,通过迭代应用jsonb_set实现批量更新,增删更新项只需修改值列表即可:
WITH updates(path, new_val) AS ( VALUES ('{food, type, name}'::text[], '"not bad"'::jsonb), ('{food, category, id}'::text[], '1'::jsonb), -- 新增更新项直接加一行即可,无需调整其他逻辑 ('{food, others, descr}'::text[], '"New description"'::jsonb) ), updated AS ( SELECT id, -- 替换为你的表主键 jsonb_set(food_value, path, new_val) AS new_food_value FROM rating CROSS JOIN updates WHERE 1=1 -- 补充你的筛选条件 ) UPDATE rating r SET food_value = new_food_value FROM updated u WHERE r.id = u.id;
方案3:深度合并(最易用,长期使用首选)
PostgreSQL 16及以上版本
原生支持jsonb_merge_patch函数,实现深度合并,仅覆盖指定字段,完全保留原有结构:
UPDATE rating SET food_value = jsonb_merge_patch( food_value, -- 直接按原JSON结构写要更新的字段即可,非常直观 '{"food": {"type": {"name": "not bad"}, "category": {"id": 1}}'::jsonb ) WHERE 1=1 -- 补充你的筛选条件 ;
PostgreSQL 9.5及以上版本
先创建自定义深度合并函数,一次创建终身可用:
CREATE OR REPLACE FUNCTION jsonb_deep_merge(target jsonb, patch jsonb) RETURNS jsonb AS $$ BEGIN IF jsonb_typeof(target) = 'object' AND jsonb_typeof(patch) = 'object' THEN RETURN ( SELECT jsonb_object_agg( key, CASE WHEN target->key IS NULL THEN patch->key WHEN patch->key IS NULL THEN target->key ELSE jsonb_deep_merge(target->key, patch->key) END ) FROM jsonb_object_keys(COALESCE(target, '{}'::jsonb) || COALESCE(patch, '{}'::jsonb)) AS key ); ELSE RETURN patch; END IF; END; $$ LANGUAGE plpgsql IMMUTABLE;
函数创建完成后,用法和原生jsonb_merge_patch完全一致:
UPDATE rating SET food_value = jsonb_deep_merge( food_value, '{"food": {"type": {"name": "not bad"}, "category": {"id": 1}}'::jsonb ) WHERE 1=1 -- 补充你的筛选条件 ;
内容的提问来源于stack exchange,提问作者Mr.P
相关产品推荐
相关产品推荐

