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 table1
("goal_id" int, "goal_owner" int, "goal_create_at" int, "goal_metrics" json)
;
INSERT INTO table1
("goal_id", "goal_owner", "goal_create_at", "goal_metrics")
VALUES
(69660, 148191, 1566576355, '[{"metric_owner": "148191", "metric_create_at": "1566576374", "metric_target": "10"}, {"metric_owner": "148191", "metric_create_at": "1566576403", "metric_target": "1"}, {"metric_owner": "148191", "metric_create_at": "1566576428", "metric_target": "3"}, {"metric_owner": "148191", "metric_create_at": "1566576450", "metric_target": "3"}]'),
(68443, 146270, 1565911160, '[{"metric_owner": "144534", "metric_create_at": "1565911175", "metric_target": "1"}, {"metric_owner": "144352", "metric_create_at": "1565911191", "metric_target": "1"}, {"metric_owner": "144352", "metric_create_at": "1565911212", "metric_target": "15"}, {"metric_owner": "146270", "metric_create_at": "1565911353", "metric_target": "23"}]'),
(68440, 146270, 1565910356, '[{"metric_owner": "144061", "metric_create_at": "1565910380", "metric_target": "3"}, {"metric_owner": "144061", "metric_create_at": "1565910462", "metric_target": "0"}, {"metric_owner": "144534", "metric_create_at": "1565910523", "metric_target": "1"}, {"metric_owner": "143866", "metric_create_at": "1565911422", "metric_target": "6"}]'),
(68442, 146270, 1565910746, '[{"metric_owner": "143866", "metric_create_at": "1565910863", "metric_target": "9"}, {"metric_owner": "143866", "metric_create_at": "1565910881", "metric_target": "1"}, {"metric_owner": "144534", "metric_create_at": "1565910905", "metric_target": "1"}, {"metric_owner": "146270", "metric_create_at": "1565910927", "metric_target": "1"}, {"metric_owner": "144534", "metric_create_at": "1565910956", "metric_target": "1"}]')
;
CREATE TABLE
INSERT 0 4
SELECT
goal_id,
(elems ->> 'metric_owner')::int AS metric_owner,
(elems ->> 'metric_create_at')::int AS metric_create_at,
(elems ->> 'metric_target')::int AS metric_target
FROM
table1,
json_array_elements(goal_metrics) as elems
| goal_id | metric_owner | metric_create_at | metric_target |
|---|---|---|---|
| 69660 | 148191 | 1566576374 | 10 |
| 69660 | 148191 | 1566576403 | 1 |
| 69660 | 148191 | 1566576428 | 3 |
| 69660 | 148191 | 1566576450 | 3 |
| 68443 | 144534 | 1565911175 | 1 |
| 68443 | 144352 | 1565911191 | 1 |
| 68443 | 144352 | 1565911212 | 15 |
| 68443 | 146270 | 1565911353 | 23 |
| 68440 | 144061 | 1565910380 | 3 |
| 68440 | 144061 | 1565910462 | 0 |
| 68440 | 144534 | 1565910523 | 1 |
| 68440 | 143866 | 1565911422 | 6 |
| 68442 | 143866 | 1565910863 | 9 |
| 68442 | 143866 | 1565910881 | 1 |
| 68442 | 144534 | 1565910905 | 1 |
| 68442 | 146270 | 1565910927 | 1 |
| 68442 | 144534 | 1565910956 | 1 |
SELECT 17
SELECT
goal_id,
elems.*
FROM
table1,
json_to_recordset(goal_metrics) AS elems(metric_owner int, metric_create_at int,metric_target int)
| goal_id | metric_owner | metric_create_at | metric_target |
|---|---|---|---|
| 69660 | 148191 | 1566576374 | 10 |
| 69660 | 148191 | 1566576403 | 1 |
| 69660 | 148191 | 1566576428 | 3 |
| 69660 | 148191 | 1566576450 | 3 |
| 68443 | 144534 | 1565911175 | 1 |
| 68443 | 144352 | 1565911191 | 1 |
| 68443 | 144352 | 1565911212 | 15 |
| 68443 | 146270 | 1565911353 | 23 |
| 68440 | 144061 | 1565910380 | 3 |
| 68440 | 144061 | 1565910462 | 0 |
| 68440 | 144534 | 1565910523 | 1 |
| 68440 | 143866 | 1565911422 | 6 |
| 68442 | 143866 | 1565910863 | 9 |
| 68442 | 143866 | 1565910881 | 1 |
| 68442 | 144534 | 1565910905 | 1 |
| 68442 | 146270 | 1565910927 | 1 |
| 68442 | 144534 | 1565910956 | 1 |
SELECT 17
CREATE TYPE metrics_type
AS (metric_owner int, metric_create_at int,metric_target int);
SELECT
goal_id,
elems.*
FROM
table1,
json_populate_recordset(null::metrics_type, goal_metrics) AS elems
CREATE TYPE
| goal_id | metric_owner | metric_create_at | metric_target |
|---|---|---|---|
| 69660 | 148191 | 1566576374 | 10 |
| 69660 | 148191 | 1566576403 | 1 |
| 69660 | 148191 | 1566576428 | 3 |
| 69660 | 148191 | 1566576450 | 3 |
| 68443 | 144534 | 1565911175 | 1 |
| 68443 | 144352 | 1565911191 | 1 |
| 68443 | 144352 | 1565911212 | 15 |
| 68443 | 146270 | 1565911353 | 23 |
| 68440 | 144061 | 1565910380 | 3 |
| 68440 | 144061 | 1565910462 | 0 |
| 68440 | 144534 | 1565910523 | 1 |
| 68440 | 143866 | 1565911422 | 6 |
| 68442 | 143866 | 1565910863 | 9 |
| 68442 | 143866 | 1565910881 | 1 |
| 68442 | 144534 | 1565910905 | 1 |
| 68442 | 146270 | 1565910927 | 1 |
| 68442 | 144534 | 1565910956 | 1 |
SELECT 17