Array aggregations


#1

Hi,

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;
EXPR$0
382241636
1 rows returned.

mapdql> select cultures from stories where cultures is null limit 3;
cultures
{}
{}
{}
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;
culture_count|culture
2504197|a
1737845|b
2783827|c
451152|d
4 rows returned.

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

Thanks in advance.
BP


#2

Anyone have any input on this?


#3

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.


#4

@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.


#5

sounds good, thanks @easy.