BigQuery中生成ARRAY或STRUCT类型数据的方法咨询

ARRAY 类型构造 #

你写的ARRAY((select distinct Somecolumn from Dataset.tablename))是BigQuery原生支持的合法语法，逻辑是将括号内单列子查询返回的所有行，直接聚合为对应类型的数组。

• 语法要求：括号内的子查询只能返回单列，返回多列会直接触发语法报错
• 运行效果：子查询返回N行结果，最终生成的数组就包含N个对应类型的元素；如果子查询返回0行，结果为空数组[]

可直接运行的示例：

SELECT 
  ARRAY(
    SELECT DISTINCT fullVisitorId 
    FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
  ) AS unique_visitor_arr

STRUCT 类型构造 #

你写的STRUCT((select distinct Somecolumn from Dataset.tablename))无法正常运行，核心原因是STRUCT是定长、强类型的结构化值，类似其他编程语言里的元组/普通对象，无法直接承载多行返回结果，需要按实际业务场景选择对应写法：

1. 子查询返回单行值，封装为单个STRUCT
   直接把需要作为STRUCT字段的标量子查询写在STRUCT括号内，为每个字段指定别名即可：

   SELECT 
     STRUCT(
       (SELECT COUNT(DISTINCT fullVisitorId) FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`) AS total_visitor,
       (SELECT COUNT(DISTINCT visitId) FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`) AS total_visit
     ) AS session_stat
   

2. 子查询返回多行结构化数据，封装为「元素为STRUCT的数组」（最常用的嵌套表场景）
   这类需求本质是构造ARRAY<STRUCT<...>>类型，需要把AS STRUCT写在ARRAY内部的子查询里，子查询返回的每一列会自动成为STRUCT的同名字段：

   SELECT 
     ARRAY(
       SELECT AS STRUCT
         device.browser,
         COUNT(DISTINCT fullVisitorId) AS visitor_cnt
       FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
       GROUP BY device.browser
     ) AS browser_stat_arr
   

补充：如果标量子查询返回0行，STRUCT中对应的字段会自动填充为NULL值。

内容的提问来源于stack exchange，提问作者vignesh