Previously I was able to quickly search ids with any type of value in an array by using cardinality > 0 in Athena.
Having switched over to BigQuery cardinality function is not supported. I tried to use HLL_COUNT.EXTRACT but that only gives me the cardinality. How can I filter to achieve the same result as cardinality > 0?
I see two different concepts in this question:
I think you want the size of an array, given the context.
This is how you check if the length of an array is more than zero:
SELECT ARRAY_LENGTH([1,2,3])>0, ARRAY_LENGTH([])>0, ARRAY_LENGTH(null)>0
# true, false, null