Help us understand the problem. What is going on with this article?

BigQueryで2つの配列をzipする方法

More than 1 year has passed since last update.

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

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)
Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away