PostgreSQL：单查询为JSONB数组添加无重复字符串

可以通过单条UPDATE语句结合PostgreSQL的JSONB函数实现需求，既保留favorites中的其他字段，又能对shapes数组去重添加元素：

UPDATE users
SET favorites = jsonb_set(
    -- 处理favorites为null的情况，确保是合法JSONB对象
    COALESCE(favorites, '{}'::jsonb),
    -- 指定要更新的JSON路径：shapes数组
    '{shapes}',
    -- 子查询实现数组去重合并
    (
        SELECT jsonb_agg(DISTINCT elem)
        FROM (
            -- 取出原shapes数组的所有元素
            SELECT elem
            FROM jsonb_array_elements(COALESCE(favorites->'shapes', '[]'::jsonb)) AS elem
            -- 合并需要新增的元素
            UNION ALL
            SELECT unnest('["Rectangle", "Circle"]'::text[])::jsonb
        ) AS combined_elements
    )
);

语句说明 #

• jsonb_set：仅修改favorites对象中shapes路径对应的值，完全保留其他字段内容，满足不能转换为字符串数组的限制。
• COALESCE：处理边界情况——如果favorites本身是null，或者shapes字段不存在/为null，会自动转为空JSON对象或空数组，避免报错。
• 子查询逻辑：
  • jsonb_array_elements将原shapes数组拆分为单个元素；
  • UNION ALL合并原元素与需要新增的Rectangle、Circle；
  • DISTINCT自动去重，确保不会添加重复元素；
  • jsonb_agg将去重后的元素重新聚合成JSONB数组。

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