【Big Query】UNNESTを使って配列内の要素をテーブル内の行に変換する

Big Queryの配列の１要素を１行に変換して使用したい

BigQuery でいう配列とは、ゼロ個以上の同じデータ型の値で構成された順序付きリストのこと

cloud.google.com

例えばこういうデータ

MY_TABLE
+----+----------+
| id | element  | 
+----+----------+
| 1  | foo      | 
|    | bar      |
|    | baz      |
| 2  | qux      |
| 3  | corge    |
|    | garply   |
|    | waldo    |
|    | fred     |
+----+----------+

これを配列内の要素をテーブル内の行に変換したい

このとき、UNNESTを使用すると良いことがわかった

https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays?hl=ja#flattening_arrays

UNNEST は ARRAY を取得し、ARRAY に含まれる各要素を 1 行にしたテーブルを返す

各行の他の列の値を維持したまま ARRAY の列全体をフラット化するには、CROSS JOIN を使用して、ARRAY 列を含むテーブルとその ARRAY 列の UNNEST 出力を結合する

SELECT *
FROM MY_TABLE
    CROSS JOIN
    UNNEST(element);

+------+----------+
| id   | element  |
+------+----------+
| 1    | foo      |
| 1    | bar      |
| 1    | baz      |
| 2    | qux      |
| 3    | corge    |
| 3    | garply   |
| 3    | waldo    |
| 3    | fred     |
+------+----------+

簡単にはこのようにして使うことができることがわかった

他参考

https://medium.com/eureka-engineering/bigquery-unnest-100percent-3d28560b4f0