PostgreSQL – Cumulative Array Aggregation

aggregatearraypostgresql

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}

Best Answer

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.

Related Question