I want to update a column of JSONB objects. So If I have this table
I want to delete value1 and value2 from the rows that have a as 1 then I thought this query would work
UPDATE
test AS d
SET
b = b - s.b_value
FROM
(VALUES
(1, 'value1'),
(1, 'value2')
)
AS s(a, b_value)
WHERE
d.a = s.a
but it gives me this result where value1 was not eliminated.
Is there a simple way to fix it? I want to make a query to delete this sort of stuff but it would be a blessing if it can be done in only one query. I got the original idea from here and here you can test the SQL query
CodePudding user response:
You can subtract a text[]
array of keys from a jsonb value like so:
with s (a, b_value) as (
values (1, 'value1'), (1, 'value2')
), dels as (
select a, array_agg(b_value) as b_values
from s
group by a
)
update test
set b = b - dels.b_values
from dels
where dels.a = test.a;
db<>fiddle here