By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table test_tablica1
(
id int,
podaci jsonb
);
insert into test_tablica1 (id, podaci)
values
(1, '[{"id": "1","firstName": "Tom","lastName": "Cruise"},
{"id": "2","firstName": "Maria","lastName": "Sharapova"},
{"id": "3","firstName": "James","lastName": "Bond"}
]');
1 rows affected
update test_tablica1
set podaci = (select jsonb_agg(case
when element @> '{"firstName": "Maria"}'
then element||'{"lastName": "Kirin"}'
else element
end)
from jsonb_array_elements(podaci) as x(element))
where podaci @> '[{"firstName": "Maria"}]';
1 rows affected
select *
from test_tablica1;
| id | podaci |
|---|---|
| 1 | [{"id": "1", "lastName": "Cruise", "firstName": "Tom"}, {"id": "2", "lastName": "Kirin", "firstName": "Maria"}, {"id": "3", "lastName": "Bond", "firstName": "James"}] |