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
– Mike Organek, Commented Aug 29, 2022 at 19:56

Mike Organek · Accepted Answer · 2022-08-29 21:02:44Z

1

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

answered Aug 29, 2022 at 21:02

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update json in postgres using multiple columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related