BigQueryのArrayに他のテーブルをjoinする

モチベーション

• Arrayをcross joinして行に展開したくない
  • 特にArrayなフィールドが複数ある場合は、後からgroup byするのが冗長でだるい

クエリ

with
orders as (
    select 
        1 as order_id, 
        [
            struct(
                1001 as product_id,
                4 as quantity
            ),
            struct(
                1003 as product_id, 
                1 as quantity
            )
        ] as items
    union all 
    select 
        2 as order_id, 
        [
            struct(
                1002 as product_id,
                2 as quantity
            ),
            struct(
                1003 as product_id,
                4 as quantity
            )
        ] as items
)
, products as (
    select 
        1001 as product_id, 
        'wallet' as name,
        30000 as price
    union all
    select 
        1002 as product_id, 
        'watch' as name,
        10000 as price
    union all
    select 
        1003 as product_id, 
        'bag' as name,
        50000 as price
)

select 
    order_id,
    array(
        select as struct
            *
        from 
            unnest(items)
            left join products using(product_id)
    ) as items
from 
    orders

1. Array をunnestしてテーブルとして扱う
2. 1.のテーブルに他のテーブルをいつも通りleft joinする
3. 2.で結合された行を Struct として扱う
4. 3.の Struct からなるテーブルを Array 化する

結果

order_id	items.product_id	items.quantity	items.name	items.price
1	1001	4	wallet	30000
	1003	1	bag	50000
2	1002	2	watch	10000
	1003	4	bag	50000

BigQuery Runner for VSCode の紹介

VSCodeでBigQueryにクエリを投げて結果を出力する拡張を使ってこの記事を書いた。
v0.0.16でクエリ結果をMarkdownのテーブルにフォーマットするオプションを追加したので、Markdownな記事を書くときに便利かも。

https://marketplace.visualstudio.com/items?itemName=minodisk.bigquery-runner