Remove jsonb array element by value
Use jsonb_set()
and the delete operator -
:
update catalog
set properties =
jsonb_set(properties, '{attributes}', (properties->'attributes') - 'is_new');
Test it in db<>fiddle.
So, I believe QRY you are looking for is:
with q as (
select distinct sku, jsonb_set(properties,'{attributes}',jsonb_agg(el) over (partition by sku),false) new_properties
from (
select
sku, jsonb_array_elements_text(properties->'attributes') as el, properties
from catalog
) p
where el != 'is_new'
)
update catalog set properties = q.new_properties from q where catalog.sku = q.sku
;
be aware that I assumed your sku
is at least UK
!
Your solution works + you pointed me to some new stuff like window functions and give me an idea for alternative solution:
WITH q as (
SELECT c.sku as sku, jsonb_set(properties, '{attributes}', jsonb_agg(el)) as new_properties
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 catalog SET properties=q.new_properties FROM q WHERE catalog.sku=q.sku;