'DISTINCT result of ARRAY_CONCAT_AGG in BigQuery

I need to compute distinct set of items from the aggregated arrays. I cannot use the array_concat as suggested in similar threads, as I don't know how many arrays I'll be concatenating. I've tried using the following, but it's not allowed in BigQuery:

select ARRAY(select DISTINCT(flattened_subjects) from UNNEST(ARRAY_CONCAT_AGG(subjects))) flattened_subjects as subjects, ... 
from ...

What works is having a nested select but I find it too cluttered:

select 
  (select ARRAY(select DISTINCT(subject) from unnest(flattened_subjects) subject) as subjects
   ...
from
   select ARRAY_CONCAT_AGG(subjects) flattened_subjects, ... from ...

Any ideas how to do it in a simple way? All I need is something like that:

select ARRAY_CONCAT_AGG(DISTINCT subjects) as subjects, ... from ...
arraysgoogle-bigqueryaggregate-functions


Solution 1:[1]

Yeah thats exactly how u do it... sucks donnit, cant even one line it cause u cant put array_concat_agg into unnest

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 user433342

Related Questions