如何从Postges中所有表行的JSONB数组中删除节点

Question

我有一个名为"书签"它包含几个标准行和一个名为"的JSONB列；列设置"；

此JSONB列的内容如下所示。

[
{
"data": "id",
"width": 25
},
{
"data": "field_1",
"width": 125
},
{
"data": "field_12",
"width": 125
},
{
"data": "field_11",
"width": 125
},
{
"data": "field_2",
"width": 125
},
{
"data": "field_7",
"width": 125
},
{
"data": "field_8",
"width": 125
},
{
"data": "field_9",
"width": 125
},
{
"data": "field_10",
"width": 125
}
]

我正在尝试编写一个更新语句，通过删除我指定的特定节点来更新此列设置。例如，我可能想更新columnsettings并仅删除data='field_2'的节点作为示例。

我试过很多东西。。。我相信它会看起来像这样，但这是错误的。

update public."Bookmarks"
set columnsettings = 
jsonb_set(columnsettings, (columnsettings->'data') - 'field_2');

在这样的JSONB数组中删除节点的正确语法是什么？

当有一行的时候，我确实得到了一个版本。这将正确更新JSONB列并删除节点

UPDATE public."Bookmarks" SET columnsettings = columnsettings - (select position-1 from public."Bookmarks", jsonb_array_elements(columnsettings) with ordinality arr(elem, position) WHERE elem->>'data' = 'field_2')::int

但是，我希望它适用于表中的每一行。当存在多于1行时，我得到错误"0"；由用作表达式"的子查询返回的多于一行；

如何获取此查询以更新表中的所有行？

---

更新，提供的答案解决了我的问题。

我现在有了另一个JSONB列，我需要在其中进行同样的筛选。结构有点不同，看起来像这个

{
"filters": [
{
"field": "field_8",
"value": [
1
],
"header": "Colors",
"uitype": 7,
"operator": "searchvalues",
"textvalues": [
"Red"
],
"displayfield": "field_8_options"
}
],
"rowHeight": 1,
"detailViewWidth": 1059
}

我尝试以如下相同的方式使用语法：

UPDATE public."Bookmarks"
SET tabsettings = filtered_elements.tabsettings
FROM (
SELECT bookmarkid, JSONB_AGG(el) as tabsettings
FROM public."Bookmarks",
JSONB_ARRAY_ELEMENTS(tabsettings) AS el
WHERE el->'filters'->>'field' != 'field_8'
GROUP BY bookmarkid
) AS filtered_elements
WHERE filtered_elements.bookmarkid = public."Bookmarks".bookmarkid;

这给出了一个错误："；不能从对象中提取元素"；

我以为我的语法是正确的，但这行应该如何格式化？

WHERE el->'filters'->>'field' != 'field_8'

我也尝试了这种格式来获取数组。这没有给出错误，但没有找到任何匹配项。。。即使有记录。

UPDATE public."Bookmarks"
SET tabsettings = filtered_elements.tabsettings
FROM (
SELECT bookmarkid, JSONB_AGG(el) as tabsettings
FROM public."Bookmarks",
JSONB_ARRAY_ELEMENTS(tabsettings->'filters') AS el
WHERE el->>'field' != 'field_8'
GROUP BY bookmarkid
) AS filtered_elements
WHERE filtered_elements.bookmarkid = public."Bookmarks".bookmarkid;

更新。

如果存在多于一个"；过滤器"；在数组中。但是，如果数组中只有一个元素应被排除，则不会删除该项。

UPDATE public."Bookmarks"
SET tabsettings = filtered_elements.tabsettings
FROM (
SELECT bookmarkid,
tabsettings || JSONB_BUILD_OBJECT('filters', JSONB_AGG(el)) as tabsettings
FROM public."Bookmarks",
-- this must be an array
JSONB_ARRAY_ELEMENTS(tabsettings->'filters') AS el
WHERE el->>'field' != 'field_8'
GROUP BY bookmarkid
) AS filtered_elements
WHERE filtered_elements.bookmarkid =  public."Bookmarks".bookmarkid;

Answer 1

您可以解构、过滤和重新构造JSONB数组。像这样的东西应该起作用：

UPDATE bookmarks
SET columnsettings = filtered_elements.columnsettings
FROM (
SELECT id, JSONB_AGG(el) as columnsettings
FROM bookmarks,
JSONB_ARRAY_ELEMENTS(columnsettings) AS el
WHERE el->>'data' != 'field_2'
GROUP BY id
) AS filtered_elements
WHERE filtered_elements.id = bookmarks.id;

使用JSONB_ARRAY_ELEMENTS，可以将JSONB数组转换为行，每个对象一行，称为el。然后您可以访问CCD_ 3属性来过滤掉"；字段2"；进入最后，通过id进行分组，将剩余的值重新组合在一起，并更新相应的行。

---

EDIT如果数据是对象中的嵌套数组，请覆盖特定键上的对象：

UPDATE bookmarks
SET tabsettings = filtered_elements.tabsettings
FROM (
SELECT id,
tabsettings || JSONB_BUILD_OBJECT('filters', JSONB_AGG(el)) as tabsettings
FROM bookmarks,
-- this must be an array
JSONB_ARRAY_ELEMENTS(tabsettings->'filters') AS el
WHERE el->>'field' != 'field_2'
GROUP BY id
) AS filtered_elements
WHERE filtered_elements.id = bookmarks.id;