BigQuery中字符串数组UNNEST拆行、类型转换及表关联问题

• 之前使用的逗号隐式连接UNNEST数组的写法，等价于内连接（INNER JOIN）逻辑：当表中记录的string_array字段为NULL、空数组[]时，无法匹配到任何数组元素，这部分记录会被直接过滤，最终仅返回数组非空的100条结果，丢失剩余900条ref_id记录。
• 原写法未做类型兼容处理，拆分出的字符串元素无法直接和INT64类型的表B字段关联，若强制强转遇到非数字格式的数组元素会直接触发查询报错。

使用LEFT JOIN UNNEST替代隐式内连接拆分数组，保证全量ref_id不丢失；搭配BigQuery原生的SAFE_CAST做安全类型转换，同时输出字符串、INT64两种格式的拆分元素，直接适配表B的关联字段类型。
可直接运行的SQL如下：

SELECT
  t.ref_id,
  -- 拆分后的原始字符串值，可直接关联表B的string类型字段
  element_str,
  -- 安全转换为INT64类型，非数字格式的元素返回NULL，无报错风险，可直接关联表B的INT64类型字段
  SAFE_CAST(element_str AS INT64) AS element_int
FROM TableA AS t
LEFT JOIN UNNEST(t.string_array) AS element_str

• 原表1000条ref_id会全部保留：数组包含元素的ref_id会按元素个数拆分为多行，数组为NULL/空的ref_id保留单行，对应的拆分元素字段值为NULL
• 若不需要保留空数组/NULL数组产生的空值行，直接在查询末尾添加WHERE element_str IS NOT NULL即可，不会丢失非空数组的拆分结果
• 两种类型的拆分字段可直接用于和表B做关联，无需额外写类型转换逻辑，非数字元素不会触发查询中断

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