CODEWITHSUNDEEP
google-bigquery
ajor
ajor

Reputation: 1634

ARRAY_AGG with STRUCT - IGNORE NULLS

I need to aggregate STRUCTs 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:

enter image description here

And I want my output to look like this, but excluding the null values within the STRUCTs:

enter image description here

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

Answers (1)

Sergey Geron
Sergey Geron

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

Related Questions