Postgres 14

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"}]