按值删除jsonb数组元素 [英] Remove jsonb array element by value
本文介绍了按值删除jsonb数组元素的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我确实弄清楚了如何从单个记录的数组中删除一个值,但是如何对其中的许多记录执行删除操作.问题在于我如何使用子查询.由于它只返回单个元素.也许我的方法是错误的.
I did figure out how to remove a value from an array for a single record, but how to do it for many of them. The problem is in the way how I use the subquery. As it has to return only single element. Maybe my approach is wrong.
Given input: '{attributes:['is_new', 'is_old']}'
Expected result '{attributes: ['is_old']}' #remove 'is_new' from jsonb array
Real example:
# sku | properties
# -------+--------------------------------
# nu3_1 | { +
# | "name": "silly_hodgkin", +
# | "type": "food", +
# | "attributes": [ +
# | "is_gluten_free", +
# | "is_lactose_free", +
# | "is_new" +
# | ] +
# | }
#Query that removes single array element:
SELECT c.sku, jsonb_agg(el) FROM
catalog c JOIN (select sku, jsonb_array_elements_text(properties->'attributes') as el from catalog) c2 ON c.sku=c2.sku where el 'is_new'
GROUP BY c.sku;
#Update query that removes single array element in single record
UPDATE catalog SET properties=jsonb_set(properties, '{attributes}', (
SELECT jsonb_agg(el) FROM
catalog c JOIN (select sku, jsonb_array_elements_text(properties->'attributes') as el from catalog) c2 ON c.sku=c2.sku
WHERE el 'is_new' AND c.sku='nu3_1'
GROUP BY c.sku
)
) WHERE sku='nu3_1';
问题又来了.如何按值删除许多数据库记录的jsonb数组元素?
The question again is. How to remove jsonb array element by value for many database records?
推荐答案
使用 jsonb_set()
和删除运算符-
:
Use jsonb_set()
and the delete operator -
:
update catalog
set properties =
jsonb_set(properties, '{attributes}', (properties->'attributes') - 'is_new');
这篇关于按值删除jsonb数组元素的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文