Issue with array_agg method when aggregating arrays of different lengths

Question

Here is a lateral query which is part of a bigger query:

lateral (
    select 
    array_agg(
            sh.dogsfilters
        ) filter (
            where 
                sh.dogsfilters is not null
        ) as dependencyOfFoods
    from 
        shelter sh
    where 
        sh.shelterid = ${shelterid}
) filtersOfAnimals,

the problem is with array_agg method as it fails when it has arrays with different lengths like this ("[[7, 9], [7, 9, 8], [8]]")!

The problem is easy to solve using json_agg but later in the query there's a any check like this:

...
where
    cd.dogsid = any(filtersOfAnimals.dependencyOfFoods)
and 
    ...
...

But as any will not work on json data which is prepared using json_agg so I can't use it instead of array_agg!

What might be a better solution to this?

Gordon Linoff · Accepted Answer · 2020-02-11 11:50:47Z

1

Unnest the arrays and re-aggregate:

lateral
(select array_agg(dogfilter) filter (where dogfilter is not null) as dependencyOfFoods
 from shelter sh cross join
      unnest(sh.dogsfilters) dogfilter
 where sh.shelterid = ${shelterid}
) filtersOfAnimals,

It is interesting that Postgres doesn't have a function that does this. BigQuery offers array_concat_agg() which does exactly what you want.

answered Feb 11, 2020 at 11:50

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Renato · Accepted Answer · 2020-02-11 11:25:24Z

1

It is ugly, but it works:

regexp_split_to_array(
          array_to_string(
             array_agg(
              array_to_string(value,',')
              ),','
          ),',')::integer[]

I don't know if this could be a valid solution from the the performance point of view ...

answered Feb 11, 2020 at 11:25

Renato

2,1771 gold badge13 silver badges25 bronze badges

1 Comment

Gordon Linoff Over a year ago

Converting an array to a string to convert back to an array of integers just doesn't seem like a good solution.

jjanes · Accepted Answer · 2020-02-11 15:12:50Z

1

In PostgreSQL, you can define your own aggregates. I think that this one does what you want:

create function array_concat_agg_tran(anyarray,anyarray) returns anyarray language sql
    as $$ select $1||$2 $$;
create aggregate array_concat_agg(anyarray) (sfunc=array_concat_agg_tran, stype=anyarray);

Then:

 select array_concat_agg(x) from (values (ARRAY[1,2]),(ARRAY[3,4,5])) f(x);
 array_concat_agg 
------------------
 {1,2,3,4,5}

With a bit more work, you could make it parallelizable as well.

answered Feb 11, 2020 at 15:12

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

1 Comment

rakibtg Over a year ago

The more I know about PostgreSQL the more it amazes me! Thanks for the details.

Collectives™ on Stack Overflow

Issue with array_agg method when aggregating arrays of different lengths

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related