PostgreSQL如何更新jsonb数组中符合指定条件的单条元素的属性

你原来的写法存在两个核心问题：

• 直接将user_details字段赋值为修改后的单个customer profile对象，完全丢失了外层的user_profile数组结构，也删除了数组内其他的admin staff配置项
• 子查询中额外关联users表会导致多行匹配风险，逻辑冗余

核心思路是展开user_profile数组，仅修改符合条件的元素后重新聚合为数组，再写回原字段：

UPDATE users
SET user_details = jsonb_set(
    user_details,
    '{user_profile}',
    (
        SELECT jsonb_agg(
            CASE 
                WHEN elem->>'profile_name' = 'customer' 
                THEN elem || '{"groups": ["group1", "group2"]}'::jsonb
                ELSE elem 
            END
        )
        FROM jsonb_array_elements(user_details->'user_profile') elem
    )
)
-- 可选过滤条件：仅更新存在customer profile的行，减少无效写入
WHERE user_details @> '{"user_profile": [{"profile_name": "customer"}]}'::jsonb;

逻辑说明 #

• 用jsonb_array_elements展开user_profile数组的所有元素
• 通过CASE分支判断：如果是profile_name为customer的元素，就用||操作符合并新的groups配置（jsonb的||会自动覆盖同名字段，不需要先删除原有groups）；其余元素保持原样
• 用jsonb_agg将处理后的所有元素重新聚合为数组
• 外层通过jsonb_set把新数组写入原user_details的user_profile路径，完整保留原有json结构的其余内容

执行后你的示例数据中，customer的groups会更新为["group1","group2"]，admin staff的配置项和外层结构均不会被修改。

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