Postgres 12

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 device (
  device_name character varying not null,
  device_data jsonb not null
);

insert into device
values
('one', '{"name": "Foo", "interfaces": [{"name": "i1", "status": "up"}, {"name": "i2", "status": "down"}]}'),
('two', '{"name": "Foo", "interfaces": [{"name": "i3", "status": "down"}]}'),
('three', '{"name": "Foo", "interfaces": [{"name": "i4", "status": "up"}]}'),
('four', '{"name": "Foo", "interfaces": [{"name": "i5", "status": "down"},  {"name": "i6", "status": "down"}]}');

4 rows affected

select d.device_name, t.down_interfaces
from device d
  cross join lateral (
     select count(*) as down_interfaces
     from jsonb_array_elements(d.device_data -> 'interfaces') as x(i)
     where i ->> 'status' = 'down'
  ) t
;


device_name
down_interfaces


one
1

two
1

three
0

four
2


select d.device_name, t.*
from device d
  cross join lateral (
     select count(*) as all_interfaces,
            count(*) filter (where i ->> 'status' = 'down') as down_interfaces
     from jsonb_array_elements(d.device_data -> 'interfaces') as x(i)
  ) t


device_name
all_interfaces
down_interfaces


one
2
1

two
1
1

three
1
0

four
2
2