Postgres 11

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
(
  id int, 
  name jsonb
);
insert into test
values
  (1, '{"a":"content1"}'),
  (1, '{"b":"content1"}'),
  (1, '{"c":"content2"}'),
  (1, '{"d":"content2"}'),
  (1, '{"e":"content3"}'),
  (1, '{"f":"content3"}'),
  (2, '["content4"]'),
  (2, '["content4"]'),
  (2, '["content5"]'),
  (2, '["content5"]');
10 rows affected

select t.id, o.content
from test t
  cross join lateral (
    (
      select content
      from jsonb_each_text(t.name) as e(key,content)
      where jsonb_typeof(t.name) = 'object'
      limit 1
    )
    union all
    (
      select *
      from jsonb_array_elements_text(t.name)
      where jsonb_typeof(t.name) = 'array'
      limit 1
    )
  ) o;


id
content


1
content1

1
content1

1
content2

1
content2

1
content3

1
content3

2
content4

2
content4

2
content5

2
content5

id	content
1	content1
1	content1
1	content2
1	content2
1	content3
1	content3
2	content4
2	content4
2	content5
2	content5