0

Does anybody know if it is possible perform cumulative array aggregation in Postgres? For example, to transform this:

create table records
(
  data text,
  path text[]
);

insert into records values
('John', '{"AAA", "BBB", "CCC"}'),
('Paul', '{"AAA", "BBB"}'),
('George', '{"AAA", "BBB", "CCC", "DDD"}'),
('Ringo', '{"EEE"}');

into this:

John    {AAA}
John    {AAA,BBB}
John    {AAA,BBB,CCC}
Paul    {AAA}
Paul    {AAA,BBB}
George  {AAA}
George  {AAA,BBB}
George  {AAA,BBB,CCC}
George  {AAA,BBB,CCC,DDD}
Ringo   {EEE}
Ringo   {EEE,FFF}
0

You want to use window functions.

select data, array_agg(unnest) over (partition by data order by ordinality)
 from records, unnest(path) with ordinality 

In this case, the default "framing" does exactly what you want: from the beginning to the current row; so you don't have to include a framing clause.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.