ARRAY_AGG with STRUCT - IGNORE NULLS

Question

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:

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

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

Sergey Geron · Accepted Answer · 2021-03-25 12:43:46Z

9

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

answered Mar 25, 2021 at 12:43

Sergey Geron

10.5k4 gold badges28 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ajor Over a year ago

Such a simple solution, should have thought of that! Thanks. :)

Collectives™ on Stack Overflow

ARRAY_AGG with STRUCT - IGNORE NULLS

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related