重复字段的聚合唯一值

下面是BigQuery标准SQL

#standardSQL
SELECT name, ARRAY_AGG(DISTINCT color) color
FROM `project.dataset.your_table`, UNNEST(color) color
GROUP BY name

你可以用问题中的虚拟数据来测试和玩上面的游戏

#standardSQL
WITH `project.dataset.your_table` AS (
  SELECT * FROM UNNEST([
    STRUCT("foo" AS name, ["red", "blue"] AS color)
  , STRUCT("foo", ["blue"])
  , STRUCT("foo", NULL)
  , STRUCT("foo", ["green"])
  , STRUCT("bar", ["orange", "black"])
  , STRUCT("bar", ["black", "white"])
]))
SELECT name, ARRAY_AGG(DISTINCT color) color
FROM `project.dataset.your_table`, UNNEST(color) color
GROUP BY name

结果是

Row name    color    
1   bar     orange   
            white    
            black    
2   foo     red  
            blue     
            green    

这将得到您想要的结果:

select t.name, array_agg(distinct color)
from (select name, array_concat_agg( color) as colors
      from t
      group by name
     ) t cross join
     unnest(colors) color
group by t.name;

查询有几个问题,特别是 unnest() 将返回多行。