Postgres - 从jsonb数组中删除元素

I have an array of jsonb elements (jsonb[]), with id and text. To remove an element I could use:

我有一个jsonb元素数组(jsonb []),带有id和text。要删除我可以使用的元素:

UPDATE "Users" SET chats = array_remove(chats, '{"id": 2, "text": "my message"')

UPDATE“Users”SET chats = array_remove(chats,'{“id”:2,“text”:“my message”')

But I want to delete the message just by the id, cause getting the message will cost me another query.

但我想删除id只是消息,因为获取消息将花费我另一个查询。

1 个解决方案

#1

Assuming missing information:

假设缺少信息:

Your table has a PK called user_id.

你的表有一个叫做user_id的PK。

You want to remove all elements with id = 2 across the whole table.

您希望在整个表中删除id = 2的所有元素。

You don't want to touch other rows.

您不想触摸其他行。

id is unique within each array of chats.

id在每个聊天数组中都是唯一的。

UPDATE "Users" u
SET    chats = array_remove(u.chats, d.chat)
FROM  (
   SELECT user_id, chat
   FROM   "Users", unnest(chats) chat
   WHERE  chat->>'id' = '2'
   ) d
WHERE  d.user_id = u.user_id;

The following explanation matches the extent of provided information in the question:

以下说明与问题中提供的信息范围相符:

#1