示例数据(请注意,我添加了一个主键 id
以便更新表):
create table product(id int primary key, product_category jsonb);
insert into product values
(1,
'{
"categoryList": [{
"category_menu_id": "51",
"is_featured_product": 0
}, {
"category_menu_id": "54",
"is_featured_product": 1
}]
}');
这个查询从json数组中跳过了具有"category_menu_id": "51"
的元素:
select jsonb_build_object('categoryList', jsonb_agg(value))
from product,
jsonb_array_elements(product_category->'categoryList')
where value->>'category_menu_id' <> '51';
jsonb_build_object
{"categoryList": [{"category_menu_id": "54", "is_featured_product": 1}]}
(1 row)
使用上述查询语句来更新表格:
update product p
set product_category = (
select jsonb_build_object('categoryList', jsonb_agg(value))
from product ps,
jsonb_array_elements(product_category->'categoryList')
where ps.id = p.id
and value->>'category_menu_id' <> '51')
returning *;
id | product_category
1 | {"categoryList": [{"category_menu_id": "54", "is_featured_product": 1}]}
(1 row)