英文:
How to remove/update a JSONB array element where key equals a value?
问题
我想从一个JSONB数组中删除/替换一个属性等于特定值的元素。我找到了许多可以实现这个目标的函数，但我想知道是否有一种方法可以在没有这些函数的情况下完成，因为我受到了数据库的限制？
这是一个示例JSONB值：
[
{ "ID": "valuea" },
{ "ID": "valueb" },
{ "ID": "valuec" }
]
我想删除数组中第二个位置的元素，其中ID等于valueb，并且希望能够使用单个更新语句完成。我想象这可以通过查找数组中的位置/顺序，然后使用jsonb_set()来删除它来实现。
如果有一种方法可以更新行而不仅仅是删除它，那将非常有帮助。可能是一个类似的查询，再次使用jsonb_set()。
英文:
I'd like remove/replace an element from a JSONB array where a property is equal to a set value. I've found a number of functions that will accomplish this but I'd like to know if there's a way to do it without one as I have database restrictions?
Here's an example JSONB value:
[
{ "ID": "valuea" },
{ "ID": "valueb" },
{ "ID": "valuec" }
]
I'd like to remove the second array position where ID is equal to valueb with a single update statement. I'd imagine this could finding the position/order in the array, jsonb_set() to remove it.
It would also be helpful if there was a way to update the row and not just remove it. Likely a similar query, again with jsonb_set().
答案1
得分: 1
很遗憾，截至到Postgres 15，还没有返回JSON数组元素位置的函数。
要删除单个匹配元素：
UPDATE tbl t
SET js = t.js - (SELECT j.ord::int - 1
FROM jsonb_array_elements(t.js) WITH ORDINALITY j(v,ord)
WHERE j.v = '{ "ID": "valueb" }'
LIMIT 1)
WHERE t.js @> '[{ "ID": "valueb" }]' -- 可选
AND jsonb_typeof(t.js) = 'array'; -- 可选
这个UPDATE使用了带有jsonb_array_elements()的相关子查询。
关于WITH ORDINALITY：
两个WHERE子句都是可选的。
-
使用过滤器
t.js @> '[{ "ID": "valueb" }]'来阻止（潜在的昂贵）空更新并充分利用jsonb列上的现有GIN索引 -
使用过滤器
jsonb_typeof(t.js) = 'array'只抑制非数组的错误。
请注意，外部过滤器包含封闭数组装饰符[]，而解除嵌套后的内部过滤器不包含这些装饰符。
要删除所有匹配元素：
UPDATE tbl t
SET js = (SELECT jsonb_agg(j.v)
FROM jsonb_array_elements(t.js) j(v)
WHERE NOT j.v @> '{ "ID": "valueb" }')
WHERE t.js @> '[{ "ID": "valueb" }]';
第二个查询从剩余元素中聚合了一个新数组。
这次，内部过滤器使用@>而不是=，以允许附加键。选择适当的过滤器。
此外：如果所涉及的数组实际上是嵌套的，与您的示例不同，可能会有用的是 jsonb_set()。
英文:
Unfortunately, there is no function to return the position of a JSON array element (yet) as of Postgres 15.
To remove a single matching element:
UPDATE tbl t
SET js = t.js - (SELECT j.ord::int - 1
FROM jsonb_array_elements(t.js) WITH ORDINALITY j(v,ord)
WHERE j.v = '{"ID": "valueb"}'
LIMIT 1)
WHERE t.js @> '[{"ID": "valueb"}]' -- optional
AND jsonb_typeof(t.js) = 'array'; -- optional
This UPDATE uses a correlated subquery with jsonb_array_elements().
About WITH ORDINALITY:
Both WHERE clauses are optional.
-
Use the filter
t.js @> '[{"ID": "valueb"}]'to suppress (potentially expensive!) empty updates and make good use of an existing GIN index on thejsonbcolumn -
Use the filter
jsonb_typeof(t.js) = 'array'to only suppress errors from non-arrays.
Note how the outer filter includes enclosing array decorators [], while the inner filter (after unnesting) does not.
To remove all matching elements:
UPDATE tbl t
SET js = (SELECT jsonb_agg(j.v)
FROM jsonb_array_elements(t.js) j(v)
WHERE NOT j.v @> '{"ID": "valueb"}')
WHERE t.js @> '[{"ID": "valueb"}]';
The second query aggregates a new array from remaining elements.
This time, the inner filter uses @> instead of = to allow for additional keys. Chose the appropriate filter.
Aside: jsonb_set() might be useful additionally if the array in question is actually nested, unlike your example.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论