PostgreSQL中如何根据Name字段删除json[]数组内指定JSON对象

你当前使用的是PostgreSQL的json[]数组类型存储购物车商品，要按JSON对象内的Name字段匹配删除对应元素，通过「拆分数组-过滤元素-重组数组」的逻辑即可实现，不需要额外安装扩展。

全版本通用写法 #

该写法兼容所有支持JSON类型的PostgreSQL版本，比如要删除Name值为Hat的数组元素，执行以下语句：

UPDATE shopping_cart
SET items = ARRAY(
    SELECT item
    FROM unnest(items) AS item
    WHERE item->>'Name' != 'Hat'
)
WHERE dod_id = '263748598';

如果需要一次删除多个匹配的元素，比如同时删除Name为1、Hat的项，调整过滤条件即可：

UPDATE shopping_cart
SET items = ARRAY(
    SELECT item
    FROM unnest(items) AS item
    WHERE item->>'Name' NOT IN ('Hat', '1', '3')
)
WHERE dod_id = '263748598';

避坑提示 #

• 不要直接用array_remove函数，该函数仅支持整个数组元素的全等匹配，无法解析JSON内部的字段属性，不能满足按Name匹配删除的需求。
• 如果后续需要频繁对数组内的JSON对象做增删改查，更建议将items字段调整为jsonb类型（直接存储JSON数组，而非用PostgreSQL原生数组包裹JSON值），这类场景下JSONB的生态函数更丰富，查询和更新性能也更好。如果是jsonb类型存储JSON数组，删除匹配元素可以用更简洁的写法：

-- 仅适用于items为jsonb类型、直接存储JSON数组的场景
UPDATE shopping_cart
SET items = items #- ARRAY[(
    SELECT ordinality - 1
    FROM jsonb_array_elements(items) WITH ORDINALITY t(elem, ordinality)
    WHERE elem->>'Name' = 'Hat'
)::text]
WHERE dod_id = '263748598';

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