Update json in postgres using multiple columns

Question

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

You are trying to update the same row twice in a single `update`. That leads to inconsistent results. — Mike Organek, Aug 29 '22 at 19:56

score 1 · Accepted Answer · answered Aug 29 '22 at 21:02

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

Update json in postgres using multiple columns

1 Answers1