BigQuery中如何处理嵌套结构：移除bag并转换数组列格式

以下是针对vendordatacosts和gdprconsentedpurposes两列，从含bag的嵌套格式转换为简化格式的具体SQL实现：

完整转换查询示例 #

SELECT
  -- 转换 gdprconsentedpurposes 为 ARRAY<INT64>
  ARRAY(
    SELECT array_element
    FROM UNNEST(gdprconsentedpurposes.bag)
  ) AS gdprconsentedpurposes,
  -- 转换 vendordatacosts 为目标嵌套数组格式
  ARRAY(
    SELECT AS STRUCT
      array_element.vendorid,
      -- 处理嵌套的 tokens 字段
      ARRAY(
        SELECT AS STRUCT
          token_item.array_element.token,
          token_item.array_element.costcpm
        FROM UNNEST(array_element.tokens.bag) AS token_item
      ) AS tokens,
      array_element.costcpm,
      array_element.revenuecpm,
      array_element.feetype
    FROM UNNEST(vendordatacosts.bag)
  ) AS vendordatacosts
FROM
  `your-project.your-dataset.your-table` -- 替换为实际表路径

各字段转换说明 #

1. gdprconsentedpurposes 转换 #

原类型是嵌套的STRUCT<bag ARRAY<STRUCT<array_element INT64>>>，通过以下步骤转换为ARRAY<INT64>：

• 使用UNNEST(gdprconsentedpurposes.bag)展开外层的bag数组，得到每个包含array_element的结构体；
• 用ARRAY()聚合所有array_element的值，直接得到纯整数数组。

2. vendordatacosts 转换 #

原类型包含两层嵌套的bag结构，需要逐层处理：

• 外层：UNNEST(vendordatacosts.bag)展开最外层的bag数组，获取每个array_element结构体；
• 内层tokens：对每个array_element中的tokens字段，用UNNEST(tokens.bag)展开其内部的bag数组，提取token和costcpm字段，再用ARRAY()聚合成目标的ARRAY<STRUCT<token STRING, costcpm FLOAT64>>；
• 最后用ARRAY(SELECT AS STRUCT ...)将所有处理后的结构体重新组合成目标数组类型。

内容的提问来源于stack exchange，提问作者d-_-b