Reputation: 1634
I need to aggregate STRUCT
s into an array where the two fields within the STRUCT
can be null (they will either both be null nor neither). Using IGNORE NULLS
in the ARRAY_AGG
function doesn't work - is there a way to take these null rows out of the resulting array?
My table looks like this:
And I want my output to look like this, but excluding the null values within the STRUCT
s:
Where there is no value for label, language or dtk I still want to include the row but with a blank array - this means I can't prefilter the nulls out as this will result in rows being excluded.
The query I am currently using to get this output looks like this:
SELECT
ARRAY_AGG(STRUCT(label, language_name) IGNORE NULLS) AS label,
ARRAY_AGG(DISTINCT dtk IGNORE NULLS) AS dtk,
country_name, category, age_group, gender
FROM
categoryData
GROUP BY
country_name, category, age_group, gender
Upvotes: 4
Views: 4186
Reputation: 10232
Try IF:
SELECT
ARRAY_AGG(if(label is not null or language_name is not null, struct(label, language_name), null) IGNORE NULLS) AS label,
ARRAY_AGG(DISTINCT dtk IGNORE NULLS) AS dtk,
country_name, category, age_group, gender
FROM
categoryData
GROUP BY
country_name, category, age_group, gender
Upvotes: 8