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}
Ringo   {EEE}
Ringo   {EEE,FFF}

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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