sql - 聚合JSON数组-之前的所有行

对于每个cust_id，我想将array1列聚合为每个shape_id之后创建的所有行。
注意array1列是json！

cust_id  |  shape_id  | array1    | created_at              |
-------------------------------------------------------------
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  |

我试过下面的代码了，我不知道下一步该怎么做。

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');

select
r.cust_id,
r.shape_id,
r.created_at,
json_agg(r.json_array_elements)
from
(
    select
    cust_id,
    shape_id,
    json_array_elements(array1),
    created_at,
    rank() over (partition by shape_id order by created_at asc) as rnk
    from a
) r
group by r.cust_id,r.shape_id,r.created_at;

实际结果：

123,1,'2019-07-23 13:42:33+10','[2, 1, 3]'
456,1,'2019-07-23 13:44:52+10','[3, 4, 5]'
555,2,'2019-07-26 13:48:11+10','[5, 3, 2, 4]'
789,1,'2019-07-23 13:48:11+10','[10, 2, 11]'
888,2,'2019-07-27 13:48:11+10','[1]'
982,3,'2019-07-23 13:48:11+10','["x"]'

预期结果：

cust_id  |  shape_id  | array1    | created_at              |    array2
------------------------------------------------------------------------------
123      | 1          | [1,2,3]   | 2019-07-23 13:42:33+10  | [3,4,5,1,10,11]
456      | 1          | [3,4,5]   | 2019-07-23 13:44:52+10  | [2,10,11]
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  | [1]
888      | 2          | [1]       | 2019-07-27 13:48:11+10  |
982      | 3          | ["x"]     | 2019-07-23 13:48:11+10  |

有人能帮我吗？
提前谢谢。。。

最佳答案

可以使用关联子查询选择具有相同形状ID和较大时间戳的所有记录数组的所有元素。

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;

db<>fiddle

关于sql - 聚合JSON数组-之前的所有行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57318236/