在BigQuery中构建含嵌套数组的结构时遇ARRAY_AGG使用问题

问题分析 #

你的代码存在两个核心问题：

1. 数组无法作为分组键：BigQuery中数组属于不可哈希的复杂类型，不能直接用GROUP BY Items（Items为数组）进行分组，这会触发语法错误。
2. ARRAY_AGG语法错误：ARRAY_AGG(Items, SubItems)写法不符合函数要求，该函数仅接受单个表达式，需要用STRUCT()将多个字段组合成单个结构后再聚合。

正确实现方案 #

根据你的需求（构建包含Items和SubItems的嵌套结构），分两种场景给出实现代码：

场景1：原始数据为扁平化结构（无预先聚合的数组） #

假设原始表包含MacroItems、Item、SubItem三个字段，先按Item聚合生成SubItems数组，再按MacroItems聚合生成包含Item和SubItems的嵌套数组：

WITH item_subitems AS (
  SELECT
    MacroItems,
    Item,
    ARRAY_AGG(SubItem) AS SubItems
  FROM `your-project.your-dataset.raw_table`
  GROUP BY MacroItems, Item
),
nested_result AS (
  SELECT
    MacroItems,
    ARRAY_AGG(STRUCT(Item, SubItems)) AS Items
  FROM item_subitems
  GROUP BY MacroItems
)
SELECT * FROM nested_result;

场景2：原始数据已包含数组字段 #

如果原始表中已经有Items（字符串数组）和SubItems（数组的数组），需要先通过UNNEST展开数组并关联对应关系，再重新聚合：

WITH unnested_data AS (
  SELECT
    MacroItems,
    Item,
    SubItem
  FROM `your-project.your-dataset.raw_table`,
  UNNEST(Items) AS Item WITH OFFSET item_pos,
  UNNEST(SubItems) AS SubItem WITH OFFSET subitem_pos
  WHERE item_pos = subitem_pos -- 确保Item与对应的SubItems数组元素位置匹配
),
item_subitems AS (
  SELECT
    MacroItems,
    Item,
    ARRAY_AGG(SubItem) AS SubItems
  FROM unnested_data
  GROUP BY MacroItems, Item
),
nested_result AS (
  SELECT
    MacroItems,
    ARRAY_AGG(STRUCT(Item, SubItems)) AS Items
  FROM item_subitems
  GROUP BY MacroItems
)
SELECT * FROM nested_result;

关键说明 #

• 使用STRUCT(Item, SubItems)将单个Item及其对应的SubItems数组打包成结构体，再用ARRAY_AGG()聚合生成嵌套数组。
• 若原始数据的数组存在位置对应关系，必须用WITH OFFSET和位置匹配条件确保关联正确，避免数据错乱。

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