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 a
(cust_id numeric,shape_id numeric,array1 json,created_at timestamp with time zone);
insert into a
(cust_id,shape_id,array1,created_at)
values
(123,1,'[1,2,3]','2019-07-23 13:42:33+10'),
(456,1,'[3,4,5]','2019-07-23 13:44:52+10'),
(789,1,'[2,10,11]','2019-07-23 13:48:11+10'),
(555,2,'[5,4,3,2]','2019-07-26 13:48:11+10'),
(888,2,'[1]','2019-07-27 13:48:11+10'),
(982,3,'["x"]','2019-07-23 13:48:11+10');
6 rows affected
SELECT a1.cust_id,
a1.shape_id,
a1.array1,
a1.created_at,
(SELECT json_agg(jae.e)
FROM a a2
CROSS JOIN LATERAL json_array_elements(a2.array1) jae (e)
WHERE a2.shape_id = a1.shape_id
AND a2.created_at > a1.created_at) array2
FROM a a1;
| cust_id | shape_id | array1 | created_at | array2 |
|---|---|---|---|---|
| 123 | 1 | [1,2,3] | 2019-07-23 04:42:33+01 | [3, 4, 5, 2, 10, 11] |
| 456 | 1 | [3,4,5] | 2019-07-23 04:44:52+01 | [2, 10, 11] |
| 789 | 1 | [2,10,11] | 2019-07-23 04:48:11+01 | null |
| 555 | 2 | [5,4,3,2] | 2019-07-26 04:48:11+01 | [1] |
| 888 | 2 | [1] | 2019-07-27 04:48:11+01 | null |
| 982 | 3 | ["x"] | 2019-07-23 04:48:11+01 | null |