BigQueryで長さの異なる配列をzipする

以前に以下の記事で2つの配列をzipする方法を紹介しました。

長さの異なる配列に対してこの手法を使うと、zip結果は短い側の配列の長さになり、長い側の配列の末尾は消されてしまいます。

結果の長さを長い方に合わせ、足りない部分はNULLで埋めるにはどのようにすればよいでしょうか？

答え

GENERATE_ARRAYした結果とCROSS JOINする

with data as (
  select ["apple", "pear"] as fruit_en, ["林檎", "梨", "蜜柑"] as fruit_ja
)
select
  fruit_en[safe_offset(i)] as fruit_en, -- デフォルト値をNULL以外にしたい場合はIFNULLも使う
  fruit_ja[safe_offset(i)] as fruit_ja,
from data
cross join unnest(
  generate_array(
    0,
    least(array_length(fruit_en), array_length(fruit_ja) - 1)
  )
) as i

なお、leastの代わりにgreatestを使うと、元の記事にあるような長さの一番短いものに合わせたzipが行われます。