如何简化BigQuery中重复的JSON_EXTRACT系列函数写法？

针对你需要提取JSON数组中100个元素的场景，提供两种简洁且易维护的解决方案，同时修正Gemini给出的SQL错误：

错误原因分析 #

Gemini的SQL存在两个问题：

• UNNEST直接放在SELECT子句中会丢失原表行的关联关系，正确做法是使用CROSS JOIN UNNEST（或隐式逗号分隔）来关联原表行和数组元素
• 从JSON_EXTRACT_ARRAY得到的是JSON对象，不能直接通过.访问属性，必须用JSON提取函数（如JSON_EXTRACT_SCALAR）

方案1：将数组拆分为行输出（推荐用于后续分析） #

这种方式把每个数组元素拆成单独的行，保留原表所有字段，同时获取元素的索引，避免重复写100次提取逻辑：

SELECT
  original_row.* EXCEPT(DATA), -- 保留原表除DATA外的所有字段
  OFFSET(cloned_item) + 1 AS cloned_index, -- 转换为1-based索引（对应原SQL中的[1][2]）
  JSON_EXTRACT_SCALAR(cloned_item, '$.name') AS cloned_name,
  JSON_EXTRACT(cloned_item, '$.value') AS cloned_value
FROM `foo.bar.my_table` original_row,
UNNEST(JSON_EXTRACT_ARRAY(original_row.DATA, '$.cloned')) cloned_item

方案2：生成宽表保持原有列格式（如cloned_1_name） #

如果需要和原SQL输出结构一致（100组name/value列），可以用动态SQL自动生成所有提取逻辑，无需手动复制：

DECLARE sql STRING;
SET sql = (
  SELECT STRING_AGG(
    CONCAT(
      'JSON_EXTRACT_SCALAR(DATA, ''$.cloned[', idx, '].name'') AS cloned_', idx, '_name, ',
      'JSON_EXTRACT(DATA, ''$.cloned[', idx, '].value'') AS cloned_', idx, '_value'
    ),
    ', '
  )
  FROM UNNEST(GENERATE_ARRAY(1, 100)) idx -- 这里的1和100是数组的起止索引（1-based）
);
SET sql = CONCAT('SELECT ', sql, ' FROM `foo.bar.my_table`');
EXECUTE IMMEDIATE sql;

只需修改GENERATE_ARRAY(1,100)中的数字，就能灵活调整要提取的元素范围。

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