Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


create table test (
  id int primary key,
  a int,
  b jsonb
);

insert into test values
(2, 2, '{"value1": 1, "value2": 2}'),
(3, 3, '{"value1": 1, "value2": 2}'),
(4, 4, '{"value1": 1, "value2": 2}'),
(5, 5, '{"value1": 1, "value2": 2}'),
(6, 6, '{"value1": 1, "value2": 2}'),
(7, 7, '{"value1": 1, "value2": 2}'),
(1, 1, '{"value1": 1, "value2": 2}'),
(8, 1, '{"value1": 1, "value2": 2}');
8 rows affected

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;
2 rows affected

select * from test;


id
a
b


2
2
{"value1": 1, "value2": 2}

3
3
{"value1": 1, "value2": 2}

4
4
{"value1": 1, "value2": 2}

5
5
{"value1": 1, "value2": 2}

6
6
{"value1": 1, "value2": 2}

7
7
{"value1": 1, "value2": 2}

1
1
{}

8
1
{}

id	a	b
2	2	{"value1": 1, "value2": 2}
3	3	{"value1": 1, "value2": 2}
4	4	{"value1": 1, "value2": 2}
5	5	{"value1": 1, "value2": 2}
6	6	{"value1": 1, "value2": 2}
7	7	{"value1": 1, "value2": 2}
1	1	{}
8	1	{}