こんな感じに非正規化されてデータが格納されているテーブルがあったとします。

fruit_en	furuits_ja
["apple", "pear", "mandarin"]	["林檎", "梨", "蜜柑"]

このテーブルを以下のように変換するにはどうしたら、よいでしょうか？

fruit_en	furuits_ja
"apple"	"林檎"
"pear"	"梨"
"mandarin"	"蜜柑"

失敗例: `,` でJOINする

とりあえずのカンで以下のクエリを書きましたが、失敗しました。

with data as (
  select ["apple", "pear", "mandarin"] as fruit_en, ["林檎", "梨", "蜜柑"] as fruit_ja
)
select fruit_en, fruit_ja from data, unnest(data.fruit_en) as fruit_en, unnest(data.fruit_ja) as fruit_ja

結果

fruit_en	fruit_ja
apple	林檎
apple	梨
apple	蜜柑
pear	林檎
pear	梨
pear	蜜柑
mandarin	林檎
mandarin	梨
mandarin	蜜柑

これは , によるJOINがCROSS JOINなための挙動です。

正解

with offset句を使うことで、配列のINDEXを出力し、その情報を使ってJOINすることによって配列のzipを行います。

select fruit_en, fruit_ja from 
  (select fruit_en , offset from data, unnest(data.fruit_en) as fruit_en with offset as offset) join
  (select fruit_ja , offset from data, unnest(data.fruit_ja) as fruit_ja with offset as offset)
  using(offset)

失敗例: , でJOINする

正解

失敗例: `,` でJOINする