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 test_tbl 
(
  id      integer NOT NULL PRIMARY KEY,
  data  jsonb
);

insert into test_tbl (id, data) 
values
(1, '{"emails":["john@dark.com", "mark_spencer@dark.com"]}'),
(2, '{"emails":["john@dark.com", "spencer2@dark.com"]}'),
(3, '{"emails":["john@dark.com", "mark@dark.com"]}');

CREATE TABLE

INSERT 0 3

select *
from test_tbl t
where data @? '$.emails[*] ? (@ like_regex ".*spenc.*")'

id
data

1
{"emails": ["john@dark.com", "mark_spencer@dark.com"]}

2
{"emails": ["john@dark.com", "spencer2@dark.com"]}

SELECT 2

select t.*
from test_tbl t
where exists (select *
              from jsonb_array_elements_text(t.data -> 'emails') as t(email)
              where t.email like '%spence%');

id
data

1
{"emails": ["john@dark.com", "mark_spencer@dark.com"]}

2
{"emails": ["john@dark.com", "spencer2@dark.com"]}

SELECT 2

id	data
1	{"emails": ["john@dark.com", "mark_spencer@dark.com"]}
2	{"emails": ["john@dark.com", "spencer2@dark.com"]}