Postgres 10

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_table(id int8 NOT NULL, data jsonb);

CREATE TABLE

INSERT INTO test_table(id, data)
values (1, '{"external": [{"internal_1": "value_1", "internal_2": "some_value"}]}'),
       (2, '{"external": [{"internal_1": "value_2", "internal_2": "another_value"}]}'),
       (3, '{"external": [{"internal_1": "value_1", "internal_2": "other_value"}]}');
       
INSERT 0 3

SELECT jsonb_build_array(
       (data ->> 'external')::jsonb -> 0 ->> 'internal_1',
       (data ->> 'external')::jsonb -> 0 ->> 'internal_2')
  FROM test_table
  
jsonb_build_array

["value_1", "some_value"]

["value_2", "another_value"]

["value_1", "other_value"]

SELECT 3

INSERT INTO test_table(id, data)
values (4, '{"external": [{"internal_1": "value_1", "internal_2": "some_value",  "internal_3": "some_other_value" }]}')

INSERT 0 1

SELECT id, 
       jsonb_agg(js.value)
  FROM test_table
 CROSS JOIN jsonb_each_text( ((data ->> 'external')::jsonb -> 0)::jsonb) AS js
 GROUP BY id
 ORDER BY id
 
id
jsonb_agg

1
["value_1", "some_value"]

2
["value_2", "another_value"]

3
["value_1", "other_value"]

4
["value_1", "some_value", "some_other_value"]

SELECT 4

id	jsonb_agg
1	["value_1", "some_value"]
2	["value_2", "another_value"]
3	["value_1", "other_value"]
4	["value_1", "some_value", "some_other_value"]