如何独立展开BigQuery中长度不一致的数组？

可以通过生成索引序列+按索引提取数组元素的方式实现，核心是利用generate_array生成对应最大数组长度的索引，再用safe_offset安全提取元素（索引超出数组长度时返回Null），具体SQL如下：

with tbl as (
select 
  ['Unknown','Eletric','High Voltage'] AS product_category, 
  ['Premium','New'] as client_cluster
),
-- 计算两个数组的最大长度
len_calc as (
  select 
    product_category,
    client_cluster,
    greatest(array_length(product_category), array_length(client_cluster)) as max_len
  from tbl
),
-- 生成对应长度的索引序列（0基）
indexes as (
  select 
    product_category,
    client_cluster,
    idx
  from len_calc,
  unnest(generate_array(0, max_len - 1)) as idx
)
-- 按索引提取元素，短数组超出部分返回Null
select
  row_number() over() as row,
  product_category[safe_offset(idx)] as product_category,
  client_cluster[safe_offset(idx)] as client_cluster
from indexes

运行结果 #

row | product_category     | client_cluster
---------------------------------------------
1   | Unknown              | Premium
2   | Eletric              | New
3   | High Voltage         | Null

关键逻辑说明 #

• array_length：获取数组的元素个数
• greatest：取两个数组长度的最大值，确定最终展开的行数
• generate_array(0, max_len -1)：生成从0开始的连续索引（BigQuery数组是0起始索引）
• safe_offset(idx)：安全访问数组元素，当索引超出数组长度时自动返回Null，避免报错
• row_number()：生成结果的行号，匹配需求中的输出格式

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