如何从PostgreSQL 15.3的JSONB对象中移除特定嵌套数组值

你之前的第一个查询思路接近正确，但忽略了当数组过滤后无元素时，jsonb_agg会返回null，导致unsubscribe字段被设为null而非空数组。第二个查询误用了-操作符——该操作符对JSONB数组仅支持按索引删除元素，无法直接按值匹配删除。

正确的UPDATE语句 #

UPDATE tablename 
SET columnname = jsonb_set(
    columnname, 
    '{unsubscribe}', 
    -- 用COALESCE确保过滤后无元素时返回空数组
    COALESCE(
        (SELECT jsonb_agg(elem) 
         FROM jsonb_array_elements(columnname->'unsubscribe') AS elem 
         -- 用#>> '{}'直接提取JSON元素的纯文本值，避免引号处理
         WHERE elem #>> '{}' <> '123456789'),
        '[]'::jsonb
    )
)
WHERE primarykey = 'primarykey';

代码说明 #

• jsonb_array_elements(columnname->'unsubscribe')：将unsubscribe数组拆分为单个JSONB元素
• elem #>> '{}'：直接提取JSON元素的纯文本值（无需处理外层引号，直接得到123456789）
• jsonb_agg(elem)：将过滤后的元素重新聚合为JSONB数组
• COALESCE(..., '[]'::jsonb)：当过滤后无元素时，返回空数组而非null，避免字段丢失
• jsonb_set：将处理后的数组替换回原JSONB对象的unsubscribe字段

扩展场景验证 #

如果原始数据包含多个元素：

{
  "spam": [],
  "unsubscribe": ["123456789", "987654321", "111222333"]
}

执行上述语句后，unsubscribe会变为["987654321", "111222333"]，仅删除目标值，保留其他元素。

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