HOME/🔍 BigQuery/

UNNEST関数

Article Outline

|| unnest()

UNNEST を使うと、ARRAYなどの配列や、REPEATEDなカラムを、開くことができる。

select 
    fullVisitorId as UU
    , visitNumber as SS
    , struct (
          h.eventInfo.eventCategory as CATEGORY
        , h.eventInfo.eventAction as ACTION
        , h.eventInfo.eventLabel as LABEL
        , h.eventInfo.eventValue as VALUE
      ) as EVENT
    , ht.customDimensions as CUSTOM_DIMENSIONS_HITS
    , ht.experiment as EXPERIMENT
from 
    ga 
    , unnest(hits) as ht
    # unnest を用いて、親テーブルに無い値を付与
    , search_list_marge as (
        select distinct KEY_CODE, KEY_NAME from `project.datasets.search_table_20*` where _TABLE_SUFFIX = (select TODAY from ts)
        union all
        select * from
            unnest(
                array
                <struct< KEY_CODE STRING, KEY_NAME STRING>>
                [("reserve_tomorrow","明日予約可"), ("reserve_today","今日予約可")]
            )
    )

| cf.