BigQuery中实现StructOfArray(SOA)与ArrayOfStruct(AOS)的方法

测试用CTE #

with tbl as (
  select 1 as x, 'a' as y union all
  select 2, 'b'
) SELECT ...

一、结构体数组（Array of Structs, AOS）的推荐实现方式 #

你当前使用的写法是BigQuery中生成AOS的标准推荐方式之一：

with tbl as (
  select 1 as x, 'a' as y union all
  select 2, 'b'
)
SELECT ARRAY(SELECT AS STRUCT * FROM tbl)

另外还有一种更简洁的等价写法，使用ARRAY_AGG配合STRUCT(*)：

with tbl as (
  select 1 as x, 'a' as y union all
  select 2, 'b'
)
SELECT ARRAY_AGG(STRUCT(*)) as aos_result

两种写法效果完全一致，都能生成包含表中所有行结构体的数组。

二、数组结构体（Struct of Arrays, SOA）的通用实现方式 #

BigQuery没有直接通过*自动生成SOA的语法，但可以通过以下两种通用方法避免手动逐个列处理：

方法1：动态SQL生成（类型严格，适合生产场景） #

通过查询INFORMATION_SCHEMA获取表的列列表，自动生成ARRAY_AGG语句：

-- 生成SOA查询语句的示例
SELECT CONCAT(
  "WITH tbl AS (SELECT 1 AS x, 'a' AS y UNION ALL SELECT 2, 'b') ",
  "SELECT AS STRUCT ",
  STRING_AGG(CONCAT("ARRAY_AGG(", column_name, ") AS ", column_name), ", " ORDER BY ordinal_position),
  " FROM tbl"
) AS soa_query
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tbl' -- 如果是永久表，需额外指定table_schema

执行该查询会得到完整的SOA查询语句，直接运行即可得到结果。如果是在脚本中使用，还可以结合EXECUTE IMMEDIATE直接执行生成的语句：

DECLARE cols ARRAY<STRING>;
SET cols = (
  SELECT ARRAY_AGG(column_name ORDER BY ordinal_position)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'tbl'
);

EXECUTE IMMEDIATE CONCAT(
  "WITH tbl AS (SELECT 1 AS x, 'a' AS y UNION ALL SELECT 2, 'b') ",
  "SELECT AS STRUCT ",
  STRING_AGG(CONCAT("ARRAY_AGG(", col, ") AS ", col), ", " FOR col IN cols),
  " FROM tbl"
);

方法2：JSON转换（快速实现，适合验证场景） #

通过JSON序列化和提取快速生成SOA，缺点是数值类型会被转为字符串，需要额外做类型转换：

with tbl as (
  select 1 as x, 'a' as y union all
  select 2, 'b'
),
json_agg as (
  SELECT TO_JSON_STRING(ARRAY_AGG(STRUCT(*))) AS json_data
  FROM tbl
)
SELECT AS STRUCT
  ARRAY(SELECT CAST(val AS INT64) FROM UNNEST(JSON_QUERY_ARRAY(json_data, '$.x')) val) AS x,
  JSON_QUERY_ARRAY(json_data, '$.y') AS y
FROM json_agg

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