如何用Node-postgres单条PostgreSQL语句移除JSONB数组指定对象

当然可以，PostgreSQL提供了完善的JSONB处理能力，完全能在数据库层面完成这个操作，不需要先查询再更新，既减少了数据库往返次数，也能避免并发修改导致的数据不一致问题。

核心实现思路 #

利用PostgreSQL的JSONB数组拆分、过滤、聚合函数，直接在UPDATE语句中完成元素移除：

1. 使用jsonb_array_elements将JSONB数组拆分为单行的JSON对象
2. 通过WHERE子句过滤掉itemId等于目标值的元素（注意类型转换，因为->>返回字符串类型，需要转为整数和参数比较）
3. 使用jsonb_agg将过滤后的元素重新聚合成JSONB数组
4. 外层UPDATE语句直接将聚合后的数组赋值给collection列

具体SQL语句（结合node-postgres） #

await db.query(
  `UPDATE user_collection
   SET collection = (
     SELECT jsonb_agg(item)
     FROM jsonb_array_elements(collection) AS item
     WHERE (item->>'itemId')::int != $2
   )
   WHERE user_id = $1`,
  [1, 11111] // 第一个参数是user_id，第二个是要移除的itemId
);

优化：处理空数组场景 #

如果过滤后数组为空，jsonb_agg会返回null，如果你希望保留空数组而非null，可以用COALESCE函数兜底：

await db.query(
  `UPDATE user_collection
   SET collection = (
     SELECT COALESCE(jsonb_agg(item), '[]'::jsonb)
     FROM jsonb_array_elements(collection) AS item
     WHERE (item->>'itemId')::int != $2
   )
   WHERE user_id = $1`,
  [1, 11111]
);

相比两步法的优势 #

• 减少数据库与应用层的网络IO，尤其是当数组元素数量很大时
• 避免两步操作中的并发冲突（比如在查询和更新之间，其他请求修改了collection数据）
• 逻辑完全交由数据库处理，应用层代码更简洁

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