UNNESTを使うことで、配列からテーブルの行に変換することができます。
https://cloud.google.com/bigquery/sql-reference/query-syntax?hl=ja#unnest
idとnameを持つテーブルの作成
- UNNESTは、
WITH OFFSET
を利用することで、配列のindexを出力することができます。
SELECT
id,
position,
['a', 'b', 'c'][OFFSET(position)] AS name
FROM
UNNEST([1, 2, 3]) AS id WITH OFFSET AS position
UNNESTを二つFROMにつける
- 各行に対して、追加したUNNESTの中の配列の数だけ繰り返される
- 以下の例だと、9つ行ができる
SELECT
id,
name
FROM
UNNEST([1, 2, 3]) AS id,
UNNEST(['a', 'b', 'c']) AS name
Right Joinを実行
SELECT
t1.id AS id1,
t1.val AS val1,
t2.id AS id2,
t2.val AS val2
FROM
(
SELECT
id,
['a', 'b', 'd'][OFFSET(position)] AS val
FROM
UNNEST([1, 2, 5]) AS id WITH OFFSET AS position
) t2
right JOIN
(
SELECT
id,
['a', 'b', 'c'][OFFSET(position)] AS val
FROM
UNNEST([1, 2, 3]) AS id WITH OFFSET AS position
) t1
ON
t1.id = t2.id