BigQuery の unnest の使い方メモ

BigQuery

Array型のカラムを含むテーブルに対するクエリ。

inner join

  • query
WITH sample AS (
  select '1' as col1, ['a', 'b'] as col2
  UNION ALL
  select '2' as col1, [] as col2
)

select
  *
from sample,
UNNEST (col2) as col2_unnest
  • result
Row col1 col2 col2_unnest
1 1 a a
b
2 1 a b
b

配列カラムが NULL or empty の行は無視される。

left join

  • query
WITH sample AS (
  select '1' as col1, ['a', 'b'] as col2
  UNION ALL
  select '2' as col1, [] as col2
)

select
  *
from sample
LEFT JOIN UNNEST (col2) as col2_unnest
  • result
Row col1 col2 col2_unnest
1 1 a a
b
2 1 a b
b
3 2 null

配列カラムが NULL or empty の行も出力される。UNNEST したカラムはNULL。