在BigQuery中添加/访问结构体数组的索引

你不需要提前给数组元素手动添加index字段，BigQuery的UNNEST支持直接获取元素的偏移量，一步到位实现你的需求：

SELECT
  class,
  STRING_AGG(
    'person_' || (idx + 1) || '_name = ' || name || ', person_' || (idx + 1) || '_age = ' || age,
    ', ' -- 可选：指定分隔符，默认是逗号加空格
  ) AS output
FROM
  (
    -- 你的原始数据集
    SELECT 'Blue' AS class, [STRUCT('Alice' AS name,18 AS age), STRUCT('Bob' AS name,17 AS age), STRUCT('Charlie' AS name,20 AS age)] as details
  ) t,
  UNNEST(details) WITH OFFSET AS idx -- 展开数组同时获取偏移量（从0开始）
GROUP BY class;

关键说明： #

• UNNEST(...) WITH OFFSET AS idx：这个语法会在展开数组的同时，返回每个元素在原数组中的位置（偏移量），默认从0开始计数，所以用idx + 1得到你需要的1-based索引
• 如果需要严格保证聚合后的字符串顺序和原数组一致，建议在STRING_AGG里加上ORDER BY idx，比如：

  STRING_AGG(
    'person_' || (idx + 1) || '_name = ' || name || ', person_' || (idx + 1) || '_age = ' || age,
    ', '
    ORDER BY idx
  ) AS output
  

• 这种方式不需要预先修改数组结构，直接在查询过程中动态获取索引，比你原来的子查询方式更简洁高效

如果一定要生成带index字段的数组（比如后续还有其他用途），可以用ARRAY(SELECT ... FROM UNNEST(details) WITH OFFSET)来构造：

SELECT
  class,
  ARRAY(
    SELECT STRUCT(name, age, idx + 1 AS index)
    FROM UNNEST(details) WITH OFFSET AS idx
  ) AS details
FROM
  (
    SELECT 'Blue' AS class, [STRUCT('Alice' AS name,18 AS age), STRUCT('Bob' AS name,17 AS age), STRUCT('Charlie' AS name,20 AS age)] as details
  ) t;

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