BigQuery UNNESTの書き方：ARRAYとSTRUCTの展開とGA4スキーマ・JSON型の取り出し

BigQueryのテーブルを開いたら、1つのセルに配列が丸ごと入っていた。GA4のエクスポートや計測ログを触ると必ず通る場面です。ここで手が止まる原因は、UNNESTの構文そのものより「何がARRAYで何がSTRUCTなのか」を切り分けられていないことにあります。2026年8月17日時点の公式ドキュメントをもとに、展開の書き分けとGA4スキーマの実例、JSON型との違い、そしてネストで持つべきかどうかの設計判断までを整理します。

まとめ：UNNESTは配列を行に開く演算子で結合とセットで使う

先に結論を置きます。UNNESTは配列（ARRAY）を受け取って行の集合に変換する演算子で、単体では使いません。元のテーブルの列を並べたいなら、そのテーブルとUNNESTの出力を結合します。書き方はカンマ、CROSS JOIN、相関INNER JOINのいずれでも同じ結果になり、配列が空またはNULLの行を残したいときだけLEFT JOINへ切り替える形です。

STRUCT（スキーマ上のRECORD）は展開の対象ではありません。ドットでフィールドを指すだけで届きます。UNNESTが要るのはモードがREPEATEDの列、つまりARRAYのほうでした。両方が重なった「STRUCTの配列」が最も多く、これはUNNESTしてからドットで掘ります。

JSON型はさらに別枠です。JSON型の列にUNNESTを当てても目的の形にはならず、フィールドアクセス演算子とJSON_VALUE、LAX_INT64などの変換関数で取り出します。3つの型の見分けがつけば、GA4のevent_paramsもカートの明細も同じ手順で扱えるでしょう。

ARRAYとSTRUCTの違いを押さえてUNNESTの対象を見極める

最初にスキーマの読み方を固定します。BigQueryのコンソールで見える「型」と「モード」の2つが、SQL側の何に対応するのかという対応表です。

RECORDはSTRUCT、REPEATEDモードはARRAYへ対応している

公式ドキュメントの説明は明快でした。ネストされたデータを作るには列のデータ型をRECORDに設定し、これはGoogleSQLではSTRUCT型としてアクセスします。繰り返しデータを作るには列のモードをREPEATEDに設定し、こちらはARRAY型としてアクセスする。型とモードは独立した2つの軸です。

この2軸を掛け合わせると4通りになります。RECORDでもREPEATEDでもない普通の列、RECORDだけの列（構造体1つ）、REPEATEDだけの列（スカラーの配列）、そしてRECORDかつREPEATEDの列。最後の組み合わせが公式の表現で「配列のSTRUCT型」であり、実務で出会う大半がこれでした。

UNNESTが必要になるのはモードがREPEATEDの列だけです

判断は単純です。モードがREPEATEDならUNNESTが要り、そうでなければ要りません。RECORDだけの列は行が増えないので、customer.address.city のようにドットでつないで参照するだけで済みます。

逆に、REPEATEDの列をドットで指そうとするとエラーになります。配列の中の1件を指しているのか全件なのかが決まらないためです。ここでUNNESTを挟んで行に開き、1件ずつを普通の列として扱える状態にしてから条件を書きます。

配列の配列は作れないのでSTRUCTの配列で代替する設計手順

設計側の制約も先に押さえておきます。公式は「配列の配列はサポートされていない。代わりにSTRUCTの配列を作る必要がある」と明記していました。2階建ての繰り返しを持ちたい場合は、内側をSTRUCTでくるんでから外側を配列にします。

ネストの深さにも上限があり、スキーマは15レベルを超えるRECORDのネストを含められません。加えてRECORD型はUNION、INTERSECT、EXCEPT DISTINCT、SELECT DISTINCTと互換性がないと記載されています。重複排除をかけたいテーブルにネストを持ち込むと、この非互換で行き止まりになる場面がありました。

製品としての前提から確認したい場合は、BigQueryのサーバーレス構造と採用判断を先に読んでおくと、列指向の保存形式とネスト構造が同じ話であることが見えてきます。

UNNESTの三つの書き方をカンマ結合とLEFT JOINで使い分ける

ここから構文です。同じ結果に到達する書き方が複数あるため、どれを既定にするかを先に決めておくとレビューが楽になります。

カンマ結合とCROSS JOINと相関INNER JOINは同じ意味です

公式ドキュメントは、配列を持つテーブルとその配列のUNNEST出力を相関INNER JOINで結合すると説明し、そのINNER JOINはCROSS JOINまたはカンマによるクロス結合としても表現できるとしています。つまり次の3つは同義でした。

SELECT
  e.event_name,
  p.key,
  p.value.string_value AS page_location
FROM `my_project.analytics.events` AS e,
UNNEST(e.event_params) AS p
WHERE p.key = 'page_location'

カンマの箇所を CROSS JOIN に置き換えても、INNER JOIN UNNEST(e.event_params) AS p ON TRUE と書いても結果は変わりません。行数が最も短いカンマ記法を既定にして、他の結合と混在して読みにくいときだけCROSS JOINへ明示的に書き換える、という運用に落ち着いています。

空配列とNULL配列の行を残したいときはLEFT JOINにする

ここが最初につまずく分岐でした。カンマやCROSS JOINで結合すると、配列が空またはNULLの行は結果から消えます。公式も「これらの行を含めたい場合はLEFT JOINを使う」と案内していました。

注文テーブルから明細を展開する集計で、明細ゼロの注文が結果から落ちる。母数を保ちたいならLEFT JOIN、明細が存在する行だけを見たいならカンマ結合、と目的から選びます。

SELECT
  o.order_id,
  i.sku,
  pos AS line_no
FROM `my_project.sales.orders` AS o
LEFT JOIN UNNEST(o.items) AS i WITH OFFSET AS pos
ORDER BY o.order_id, pos

WITH OFFSETを付けると配列の並び順を列として保てる

上の例に混ぜたのがWITH OFFSETです。公式は「省略可能なWITH OFFSET句を使うと、各配列要素のオフセットを持つ列を追加で返せる」と説明しています。ゼロ始まりの位置が列として手に入る形。

UNNESTした時点で配列の順序は保証されない前提に立ってください。並び順に意味があるデータ、たとえば経路の通過順や会話のターンは、WITH OFFSETで位置を取り出してからORDER BYで並べ直します。この一手間を省くと、再実行のたびに順番が変わります。

ARRAY_AGGで展開した行を元の配列へ戻して再利用する手順

逆方向も押さえておきます。公式は「ARRAY_AGGを使って値を配列に集約できる」とし、ORDER BYで要素の順序を制御できると記載しています。中間処理はフラットな行で回し、最後にネストへ畳み直すという組み方が可能です。

SELECT
  order_id,
  ARRAY_AGG(STRUCT(sku, qty) ORDER BY sku) AS items
FROM `my_project.sales.order_lines`
GROUP BY order_id

明細テーブルから注文単位のネスト構造を組み立てる形です。正規化テーブルをネストへ移す移行時の変換にも使えます。

GA4エクスポートのevent_paramsを取り出す実装の型を固める

UNNESTの用途として最も多いのがGA4のエクスポートでした。スキーマの形が独特なので、実際のフィールド名で追いかけます。

event_paramsはkeyとvalueに分かれた繰り返しRECORDです

GA4のエクスポートスキーマでは、event_paramsが繰り返し可能なRECORDで、イベントに紐づくキーごとに繰り返されます。中身はkeyがSTRING、valueがRECORDで、valueの下にstring_value（STRING）、int_value（INTEGER）、double_value（FLOAT）、float_value（FLOAT・現在は使われない）が並ぶ構造です。

つまりパラメータ名が列名ではなく行の値として入っています。page_locationを取りたければ、UNNESTで開いてから p.key = 'page_location' で絞り、値は型に応じて p.value.string_value や p.value.int_value から拾う。数値のパラメータを string_value で取ろうとしてNULLが並ぶのは、この型の取り違えでした。

複数パラメータはMAXとIFの組み合わせで横持ちに変える集約方法

実務で欲しいのは、1イベント1行に複数のパラメータが列として並んだ形です。UNNESTで縦に開いたものを、条件付き集約で横へ戻します。

SELECT
  event_date,
  event_name,
  MAX(IF(p.key = 'page_location', p.value.string_value, NULL)) AS page_location,
  MAX(IF(p.key = 'ga_session_id', p.value.int_value, NULL)) AS session_id,
  MAX(IF(p.key = 'engagement_time_msec', p.value.int_value, NULL)) AS engage_ms
FROM `my_project.analytics_123456789.events_20260817`,
UNNEST(event_params) AS p
GROUP BY event_date, event_name

キーごとにMAXとIFを1組ずつ足していくだけなので、パラメータが増えても機械的に伸ばせます。同じ型の書き方はuser_propertiesにも通用しました。こちらもkeyとvalueに分かれた繰り返しRECORDで、valueの下にset_timestamp_micros（INTEGER）が加わる点だけが違います。

event_dateは文字列でevent_timestampはマイクロ秒の整数

もう1つ、GA4のエクスポートで型を間違えやすい箇所を挙げます。event_dateはYYYYMMDD形式のSTRINGで、DATE型ではありません。event_timestampはUTCのマイクロ秒を持つINTEGERです。

日付で絞るときはPARSE_DATEで変換するか、文字列のまま比較するかを決めておきます。マイクロ秒の整数をそのままTIMESTAMPとして扱うとおよそ5万年後を指すため、TIMESTAMP_MICROSを通してから日本時間へ変換する手順を型として持っておくと事故が減りました。エクスポートの設定側から確認したい場合は、GA4とBigQueryの連携手順と費用の実額にまとめています。

itemsの配列は展開の粒度が変わるため集計を分けて書く実装方法

eコマース計測ではitemsも繰り返しRECORDとして入ってきます。ここで注意が要るのは、event_paramsとitemsを同時に展開したときの行数です。両方をカンマで並べると、パラメータ数×商品数の掛け算になります。

結果としてイベント単位の集計値が商品数だけ水増しされる。回避策は、パラメータの横持ちとitemsの展開を別のサブクエリに分け、イベントの一意キーで結合し直すことでした。1つのクエリに詰め込むより、段を分けたほうが検算もしやすくなります。

JSON型の列はUNNESTではなくアクセス演算子と変換関数で扱う

ここが誤解の集まる領域です。ネスト構造とJSON型は見た目が似ていますが、扱い方が根本的に違います。

JSON型はCREATE TABLEで宣言しドットと添字でたどる

公式ドキュメントは、CREATE TABLE文で列をJSON型として宣言する例として CREATE TABLE mydataset.table1(id INT64, cart JSON) を示しています。取り出しはフィールドアクセス演算子で、ドット記法の cart.name と添字記法の cart['name'] や cart[0] の両方が使えます。

配列の中身も添字で指せるため、要素数が決まっている取り出しにUNNESTは要りません。JSON型の列に対してUNNESTを掛けようとして型エラーになるのは、そもそも入口が違うからでした。

JSON_VALUEとJSON_QUERYはnullの返し方が食い違う

2つの関数の違いは、公式の記述がそのまま判断基準になります。JSON_QUERYはJSON型を返すため、JSONのnullをそのままJSONのnullとして返す。JSON_VALUEはスカラー値を返すため、JSONのnullはスカラー値ではないという理由でSQLのNULLを返します。

文字列として使いたいならJSON_VALUE、入れ子の一部をJSONのまま切り出して次の処理へ渡したいならJSON_QUERY。この線引きで選べば迷いません。なお、この挙動はJSON型に限った話で、公式は「STRING型に保存されたJSON値には適用されない」と注記しています。STRING列に入れたJSONへJSON_QUERYを掛けると、JSONのnullではなくSQLのNULLが返る形です。

LAX関数で入力型を問わずスカラーへ安全に落とし込む変換方法

数値や真偽値を取り出す場面では、LAX_INT64、LAX_STRING、LAX_BOOL、LAX_FLOAT64が用意されています。公式はこれらを「スカラーのSQL値へ柔軟に変換」する関数として列挙していました。

SELECT
  id,
  LAX_INT64(cart.item_count) AS item_count,
  JSON_VALUE(cart.owner.name) AS owner_name,
  JSON_QUERY(cart.lines) AS lines_json
FROM `my_project.shop.carts`

厳密なCASTと違い、外部から届くJSONのように構造が揺れうるデータでも実行時エラーで落ちにくい性質があります。

非正規化してネストで持つか結合で組むかをテーブル設計で判断する

ここまでは読む側の話でした。最後に、そもそもネストで持つべきかという設計の判断に踏み込みます。

公式が示す推奨条件は階層関係と低いカーディナリティの組み合わせ

公式のパフォーマンス設計ガイドは、ネストと繰り返しフィールドを使う条件を挙げています。親子のような階層的な関係があり、頻繁に一緒にクエリされる場合。そして一対多の関係のカーディナリティが低い場合です。

注文と明細、イベントとパラメータ、ユーザーと属性はこの条件に合致します。逆に、一対多の「多」が数万件に膨らむ関係や、片側だけを単独で更新したい関係は当てはまりません。ネストは同じ行に固めて持つ構造なので、片側だけの書き換えは行の作り直しになります。

スター型で整っている既存設計を無理にネストへ寄せない判断基準

もう1つの記述が判断を助けます。公式は、スタースキーマは通常アナリティクス向けに調整されているため、さらに非正規化を試みても性能に大きな変化が出ない可能性があるとしています。

すでにファクトとディメンションで整理された基盤に対して、ネスト化のためだけの改修を入れる価値は薄いということです。ネストへ寄せる判断は、正規化されたテーブルを結合し続けている構成、あるいは半構造化データをそのまま受けている構成に限ると考えてください。

公式の比較で処理時間と処理量の両方が下がった具体的な数値検証の結果

効果の目安も一次情報にあります。公式が示すStackOverflowデータでの比較では、JOINとGROUP BYを使う版がおよそ25秒で1.88GBの処理だったのに対し、ネストと繰り返しフィールドを使う版はおよそ10秒で1.28GBでした。理由として、非正規化によってデータが個々のスロットにローカライズされ、並列実行が効くことが挙げられています。

処理量が下がるという点は課金に直結します。BigQueryはスキャンしたバイト数で課金されるため、読む列と読む量が減れば請求も減る関係。オンデマンドと容量モデルの体系そのものは、BigQueryの料金とコスト監視の実務で扱っています。

ネスト構造は更新頻度と参照パターンの両面から見積もる判断基準

設計時に確認すべきは2点です。1つは更新頻度で、明細だけを頻繁に書き換えるならネストは不利。もう1つは参照パターンで、親だけを見るクエリが大半なら、明細をネストで抱えていても読まれない列として置いておけます。

この2点を取り込みの設計と合わせて決めるのが順序でした。収集から保存、そして分析までの構成をまとめて設計したい場合は、データ分析基盤構築・MLOps構築支援で要件整理から実装まで請け負っています。

UNNESTを使う場面と使うべきでない場面を条件付きで言い切る

判断を条件の形にして締めます。どちらが優れているかではなく、どの条件でどちらを選ぶかです。

展開後に絞らず対象を絞ってから展開する効率的なクエリ実行順序

まず書き方の原則です。UNNESTは行を増やす操作なので、増やしてから条件で減らすと中間の行数が跳ねます。日付パーティションやイベント名の条件は、UNNESTより前の段階、つまり元テーブルのWHERE句に置いてください。日付側の丸め方とタイムゾーンの指定は、BigQuery DATE_TRUNCの粒度指定とタイムゾーンにまとめました。

GA4のようにテーブルが日付でシャーディングされている場合は、対象のテーブル群を先に絞る形が効きます。展開は絞り込んだあとの最小の集合に対して掛ける、という順序を守るだけで実行時間の桁が変わりました。

UNNESTを使う条件は一対多のデータを一緒に読む参照パターン

ネストで持ち、UNNESTで開く構成を選んでよいのは、次の3条件がそろう場合です。第一に、親と子が常に一緒に参照されること。第二に、子の件数が1親あたり数十件程度に収まること。第三に、子だけを個別に更新する運用がないこと。

GA4のイベントとパラメータ、注文と明細、記事とタグはこの3条件を満たします。読むときは必ずセットで、件数は限定的で、更新は洗い替え。ここに当てはまるならネストは有利に働きます。

別テーブルへ切り出す条件はデータの更新と共有が起きる運用形態

逆に、次のいずれかに当たるならネストをやめて別テーブルに切り出してください。子側を単独で更新または削除する運用があるとき。子のデータが複数の親から共有されるとき。子の件数が1親あたり数千件を超えるとき。そして子だけを対象にした重複排除や集合演算が要るとき。

最後の条件は前述の非互換が効きます。RECORD型はSELECT DISTINCTやUNIONと互換性がないため、重複排除を前提にした処理はネスト構造の中では組み立てにくい。設計段階でこの制約に触れるなら、正規化したまま結合で組むほうが結果的に短く済みます。

外注する場合はスキーマとクエリ頻度を先に共有する事前の依頼手順

外部に設計や実装を依頼するときは、3点を先に渡すと見積もりの精度が上がります。現在のスキーマ（型とモードが分かる形）、主要なクエリの頻度と対象期間、そして取り込み側のキーが増減するかどうか。

この3点が決まれば、ネストで受けるかJSON型で受けるか、正規化のまま結合で組むかが分かれます。曖昧なまま着手すると、変換処理の書き直しが後から積み上がる形。BigQueryの初回操作からたどりたい場合は、BigQueryの使い方と初回のつまずきどころが入口です。

よくある質問

UNNESTとCROSS JOINはどちらを書くべきですか？

結果は同じなので、読みやすさで選んでください。公式は、配列を持つテーブルとUNNEST出力の結合を相関INNER JOINとして説明し、それはCROSS JOINまたはカンマによるクロス結合としても表現できるとしています。カンマ記法が最も短く、他の結合と混在して読みにくい場合だけCROSS JOINを明示するという運用が扱いやすい形でした。

UNNESTすると行が消えるのはなぜですか？

配列が空またはNULLの行が、内部結合の性質で落ちているためです。公式もこれらの行を含めたい場合はLEFT JOINを使うと案内しています。注文の件数が合わない、イベント数が減るといった症状の多くはこれで説明がつきます。母数を保ちたいならLEFT JOIN UNNESTへ切り替えてください。

GA4のパラメータが取れずNULLになるのはなぜですか？

値の型を取り違えている可能性が高いでしょう。GA4のevent_paramsはvalueの下にstring_value、int_value、double_value、float_valueが並ぶ構造で、パラメータごとに値が入るフィールドが違います。ga_session_idやengagement_time_msecは数値なのでint_value側に入り、string_valueで取るとNULLになります。

JSON型の列にUNNESTを使ってもよいですか？

入口が違うため、そのままでは目的の形になりません。JSON型はフィールドアクセス演算子のドット記法と添字記法でたどり、スカラーへの取り出しはJSON_VALUEやLAX_INT64などで行います。配列に見える部分も添字で指せるため、多くの場面でUNNESTは不要です。ネスト構造（RECORDとREPEATED）とJSON型は別物として扱ってください。

ネスト構造にすると本当にコストは下がりますか？

条件が合えば下がります。公式が示すStackOverflowデータの比較では、JOINとGROUP BYの版がおよそ25秒で1.88GB、ネストと繰り返しフィールドの版がおよそ10秒で1.28GBでした。ただし推奨条件は「階層的な関係で頻繁に一緒にクエリされる」「一対多のカーディナリティが低い」場合です。スタースキーマのように既に整った設計では、大きな変化が出ない可能性があるとも記載されています。

関連記事

• BigQueryとは：サーバーレスDWHの仕組みと採用判断：列指向とネスト構造の前提
• BigQueryの使い方：コンソールとbqコマンドの初回操作：クエリを実行するまでの手順
• BigQueryの料金：オンデマンドとEditionsの分岐点：スキャン量が課金へ効く仕組み
• BigQuery無料枠の内訳とサンドボックスの制限：検証環境を無料で回す設計
• BigQuery APIの選び方とリトライ設計：展開したデータを外へ渡す経路
• BigQuery MLでSQLだけで学習を回す：ネスト構造を学習データへ整える
• データ分析基盤の構築：5層アーキテクチャと実装手順：収集から可視化までの全体像
• GA4とBigQuery、Looker Studioの連携手順：エクスポート設定と費用の実額
• Looker Studioとは：機能と料金とBigQuery連携：横持ちにした結果の可視化