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 api_details ( raw_data JSONB NOT NULL );
INSERT INTO api_details VALUES
('{
"header": [
"#0 action_name",
"#1 sent_timestamp",
"#0 Sent",
"#1 Delivered"
],
"name": "campaign - lifetime",
"rows": [
[
"campaign_2475",
"1600416865.928737 - 1601788183.440805",
7504,
7483
],
[
"campaign_d_1084_SUN15_ex",
"1604220248.153903 - 1604222469.087918",
63095,
62961
],
[
"campaign_SUN15",
"1604222469.148829 - 1604411016.029794",
63303,
63211
]
],
"success": true
}');
1 rows affected
SELECT (j.value)->>0 AS action_name,
(j.value)->>1 AS sent_timestamp,
(j.value)->>2 AS Sent,
(j.value)->>3 AS Delivered
FROM api_details
CROSS JOIN JSONB_ARRAY_ELEMENTS(raw_data->'rows') AS j
| action_name | sent_timestamp | sent | delivered |
|---|---|---|---|
| campaign_2475 | 1600416865.928737 - 1601788183.440805 | 7504 | 7483 |
| campaign_d_1084_SUN15_ex | 1604220248.153903 - 1604222469.087918 | 63095 | 62961 |
| campaign_SUN15 | 1604222469.148829 - 1604411016.029794 | 63303 | 63211 |