如何在BigQuery中对数组执行逐元素函数操作 如加常量或类型转换

要对BigQuery中的数组执行逐元素的自定义操作，目前有两种主流实现方案，都适配你提供的测试数据场景：

方案1：全版本兼容的ARRAY() + UNNEST组合方案 #

这个方案不需要依赖特殊高阶函数，所有BigQuery环境都可运行，核心逻辑是先将数组拆分为单个元素、逐元素处理后再拼接回数组：

• 逐元素加固定常数（示例为加1.5）：ARRAY(SELECT x + 1.5 FROM UNNEST(splits) AS x)
• 逐元素转字符串类型：ARRAY(SELECT CAST(x AS STRING) FROM UNNEST(splits) AS x)

完整查询示例：

WITH
  races_with_struct AS (
  SELECT
    "800M" AS race,
    [STRUCT("Rudisha" AS name,
      [23.4, 26.3, 26.4, 26.1] AS splits),
    STRUCT("Makhloufi" AS name,
      [24.5, 25.4, 26.6, 26.1] AS splits),
    STRUCT("Lewandowski" AS name,
      [25.0, 25.7, 26.3, 27.2] AS splits),
    STRUCT("Nathan" AS name,
      ARRAY<FLOAT64>[] AS splits),
    STRUCT("David" AS name,
      NULL AS splits)] AS participants),
  races AS (
  SELECT
    race,
    participant.name AS name,
    participant.splits AS splits
  FROM
    races_with_struct r
  CROSS JOIN
    UNNEST(r.participants) AS participant)
SELECT
  *,
  -- 逐元素加1.5
  ARRAY(SELECT x + 1.5 FROM UNNEST(splits) AS x) AS splits_add_const,
  -- 逐元素转字符串
  ARRAY(SELECT CAST(x AS STRING) FROM UNNEST(splits) AS x) AS splits_cast_str
FROM
  races

该方案会自动处理边界场景：空数组运算后仍返回空数组，NULL数组运算后仍返回NULL，不会触发报错。

方案2：高阶函数ARRAY_TRANSFORM简洁方案 #

如果你使用的是新版BigQuery，可以直接调用内置的ARRAY_TRANSFORM函数实现，语法更简洁：

• 逐元素加固定常数（示例为加1.5）：ARRAY_TRANSFORM(splits, x -> x + 1.5)
• 逐元素转字符串类型：ARRAY_TRANSFORM(splits, x -> CAST(x AS STRING))

你可以把上面示例查询中的对应计算逻辑替换为以上写法，运行结果完全一致。

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