将BigQuery中重复模式的String类型转换为重复模式的Record类型

核心思路 #

通过UNNEST展开重复列并匹配元素位置，再用ARRAY_AGG将对应元素组合成STRUCT（即BigQuery中的Record类型），最后重新聚合为重复的Record字段。

场景1：三个重复列长度一致 #

如果原表中最后三个重复字符串列的元素数量完全对应，使用以下SQL：

CREATE OR REPLACE TABLE `your_project.your_dataset.target_table`
AS
SELECT
  -- 保留原表中非重复字段
  id,
  other_non_repeated_column,
  -- 组合元素为STRUCT并聚合为重复Record
  ARRAY_AGG(STRUCT(str_col1, str_col2, str_col3)) AS repeated_record
FROM
  `your_project.your_dataset.source_table`,
  -- 展开重复列并获取位置索引
  UNNEST(repeated_str_col1) AS str_col1 WITH OFFSET pos1,
  UNNEST(repeated_str_col2) AS str_col2 WITH OFFSET pos2,
  UNNEST(repeated_str_col3) AS str_col3 WITH OFFSET pos3
WHERE
  -- 确保元素按位置一一对应
  pos1 = pos2 AND pos2 = pos3
GROUP BY
  -- 按所有非重复字段分组
  id, other_non_repeated_column;

场景2：三个重复列长度不一致 #

如果存在重复列长度不匹配的情况，补全缺失元素（示例用空字符串填充），使用以下SQL：

CREATE OR REPLACE TABLE `your_project.your_dataset.target_table`
AS
SELECT
  id,
  other_non_repeated_column,
  -- 按位置排序聚合，保证元素顺序正确
  ARRAY_AGG(STRUCT(str_col1, str_col2, str_col3) ORDER BY pos) AS repeated_record
FROM (
  SELECT
    id,
    other_non_repeated_column,
    -- 补全缺失元素
    COALESCE(str_col1, '') AS str_col1,
    COALESCE(str_col2, '') AS str_col2,
    COALESCE(str_col3, '') AS str_col3,
    pos
  FROM
    `your_project.your_dataset.source_table`,
    -- 生成覆盖所有列最大长度的位置索引
    UNNEST(GENERATE_ARRAY(0, GREATEST(
      ARRAY_LENGTH(repeated_str_col1)-1,
      ARRAY_LENGTH(repeated_str_col2)-1,
      ARRAY_LENGTH(repeated_str_col3)-1
    ))) AS pos
  -- 左连接匹配每个位置的元素
  LEFT JOIN UNNEST(repeated_str_col1) AS str_col1 WITH OFFSET pos1 ON pos1 = pos
  LEFT JOIN UNNEST(repeated_str_col2) AS str_col2 WITH OFFSET pos2 ON pos2 = pos
  LEFT JOIN UNNEST(repeated_str_col3) AS str_col3 WITH OFFSET pos3 ON pos3 = pos
)
GROUP BY
  id, other_non_repeated_column;

注意事项 #

• 替换代码中的项目、数据集、表名为实际值
• 替换id、other_non_repeated_column为原表非重复字段
• 替换repeated_str_col1/2/3为需要转换的重复字符串列名
• 可按需重命名repeated_record及内部字段名

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