Let’s say I have a table t1 and the columns grouper, value, and arr_col – where arr_col is an array of text. Is there any way to get the count of distinct values across all the arrays per group and the avg at the same time?

This doesn’t work as unnest is a set based function.

select grouper, avg(value), count(distinct(unnest(arr_col)))
  from t1
 group by grouper

An alternative might be the following but I’m keen not to restructure the query too much if it can be avoided – the actual code is more complex, has semi-arbritary (single or multiple) groupings and multiple aggregates, and is using ORM – SQLAlchemy. Syntax here might not be perfect but it should be a representative example.

with (select distinct grouper, unnest(arr_col) unnested_col
        from t1) as unnested_distinct_rows,
     (select grouper, avg(value) avg_value
        from t1
       group by grouper) as avg_agg,
     (select grouper, count(*) distinct_arr_col_values
        from unnested_distinct_rows
        group by grouper) as distinct_agg
select avg_agg.grouper, avg_agg.avg_value, distinct_agg.distinct_arr_col_values
  from avg_agg
  join distinct_agg on avg_agg.grouper = distinct_agg.grouper