Array aggregations



I have an ARRAY column that either has data in the array, or does not (is empty). I need to run an aggregation on this ARRAY column such that an empty array is counted as one case, and all combinations from UNNEST(array_column) are treated as all other cases.

Using “is null” to identify empty ARRAYs seems to work…

mapdql> select count(*) from stories where cultures is null;
1 rows returned.

mapdql> select cultures from stories where cultures is null limit 3;
3 rows returned.

But when I try to combine “is null” with UNNEST(cultures) it doesn’t return rows with empty arrays. For example:

mapdql> select approx_count_distinct(p_id) as culture_count, case when cultures is null then 'unknown' when cultures is not null then UNNEST(cultures) end as culture from stories group by culture;
4 rows returned.

Is there a way to do what I’m trying to do without resorting to running two queries?

Thanks in advance.


Anyone have any input on this?


BP, sorry about the delay. You posted this on a Sunday right before the winter break, so we missed it. I’ll see what I can do about getting you more information.


@bploetz we think you may have uncovered a bug, because what you’re trying to do seems like it should work. We’re going to investigate further.

@dwayneberry suspects that it might be in how we’re handling null vs not null.

Thanks for your patience. Hopefully we’ll have some further news soon.


sounds good, thanks @easy.