Postgres 15

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 tbl (js jsonb);
insert into tbl values ('{"value": {"a": false, "b": true, "c": false, "d":true, "e":false, "f":true}}'::jsonb)

CREATE TABLE



INSERT 0 1


with cte(o_id, n_id) as (
   select 'a', 'apple'
   union all
   select 'b', 'ball'
   union all
   select 'c', 'cat'
)
update tbl set js = jsonb_build_object('value', (select jsonb_object_agg(c.n_id, v.value) 
  from jsonb_each(js -> 'value') v join cte c on c.o_id = v.key) || coalesce(
     (select jsonb_object_agg(v.key, v.value) from jsonb_each(js -> 'value') v 
      where not exists (select 1 from cte c where c.o_id = v.key)), '{}'::jsonb));

UPDATE 1


select jsonb_pretty(js) from tbl;


jsonb_pretty


{
    "value": {
        "d": true,
        "e": false,
        "f": true,
        "cat": false,
        "ball": true,
        "apple": false
    }
}



SELECT 1