I have a jsonb column with this format.
{
"categoryList": [{
"category_menu_id": "51",
"is_featured_product": 0
}, {
"category_menu_id": "54",
"is_featured_product": 1
}]
}
How to remove category by category_menu_id?
This select query is working fine by category_menu_id.
select product_category
from product
where product_category->'categoryList' @> '[{"category_menu_id": "51"}]';
Example data (note that I have added a primary key id to enable updating the table):
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
}]
}');
This query skips the element with "category_menu_id": "51" from the json array:
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)
Use the above query to update the table:
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 -- important! primary key to identify a row
and value->>'category_menu_id' <> '51')
returning *;
id | product_category
----+--------------------------------------------------------------------------
1 | {"categoryList": [{"category_menu_id": "54", "is_featured_product": 1}]}
(1 row)
