Search code examples
sqlpostgresqlsql-updatequery-optimization

Update json in postgres using multiple columns

I want to update a column of JSONB objects. So If I have this table

another 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. some table

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

Solution

  • 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