レガシー SQL でのネストされたフィールドと繰り返しフィールドのクエリ
このドキュメントでは、ネストされたデータと繰り返しデータをレガシー SQL クエリ構文でクエリする方法を詳しく説明します。BigQuery で推奨されるクエリ構文は GoogleSQL です。GoogleSQL でのネストされたデータと繰り返しデータの処理については、GoogleSQL 移行ガイドをご覧ください。
BigQuery では、JSON または Avro ファイル形式でネストされたデータと繰り返しデータの読み込みとエクスポートをサポートしています。レガシー SQL クエリの多くに対して、BigQuery は自動的にデータをフラット化します。たとえば、多くの SELECT ステートメントでは、データの構造を維持しながらネストされたフィールドまたは繰り返しフィールドを取得できます。また、WHERE 句を使用すると、構造を維持しながらデータをフィルタリングできます。逆に、ORDER BY 句と GROUP BY 句は、クエリ対象のデータを暗黙的にフラット化します。データが暗黙的にフラット化されない場合（レガシー SQL で複数の繰り返しフィールドのクエリを実行する場合など）、SQL 関数 FLATTEN および WITHIN を使用してデータのクエリを実行できます。
FLATTEN
ネストされたデータを照会する場合、BigQuery は自動的にテーブルデータをフラット化します。例として、個人データのサンプル スキーマを見てみましょう。
Last modified Schema Total Rows Total Bytes Expiration
----------------- ----------------------------------- ------------ ------------- ------------
27 Sep 10:01:06 |- kind: string 4 794
|- fullName: string (required)
|- age: integer
|- gender: string
+- phoneNumber: record
| |- areaCode: integer
| |- number: integer
+- children: record (repeated)
| |- name: string
| |- gender: string
| |- age: integer
+- citiesLived: record (repeated)
| |- place: string
| +- yearsLived: integer (repeated)いくつかの繰り返しフィールドとネストされたフィールドがあるのでご注意ください。次のようなレガシー SQL クエリを person テーブルに対して実行すると、
SELECT fullName AS name, age, gender, citiesLived.place, citiesLived.yearsLived FROM [dataset.tableId]
BigQuery は、フラット化された出力でデータを返します。
+---------------+-----+--------+-------------------+------------------------+ | name | age | gender | citiesLived_place | citiesLived_yearsLived | +---------------+-----+--------+-------------------+------------------------+ | John Doe | 22 | Male | Seattle | 1995 | | John Doe | 22 | Male | Stockholm | 2005 | | Mike Jones | 35 | Male | Los Angeles | 1989 | | Mike Jones | 35 | Male | Los Angeles | 1993 | | Mike Jones | 35 | Male | Los Angeles | 1998 | | Mike Jones | 35 | Male | Los Angeles | 2002 | | Mike Jones | 35 | Male | Washington DC | 1990 | | Mike Jones | 35 | Male | Washington DC | 1993 | | Mike Jones | 35 | Male | Washington DC | 1998 | | Mike Jones | 35 | Male | Washington DC | 2008 | | Mike Jones | 35 | Male | Portland | 1993 | | Mike Jones | 35 | Male | Portland | 1998 | | Mike Jones | 35 | Male | Portland | 2003 | | Mike Jones | 35 | Male | Portland | 2005 | | Mike Jones | 35 | Male | Austin | 1973 | | Mike Jones | 35 | Male | Austin | 1998 | | Mike Jones | 35 | Male | Austin | 2001 | | Mike Jones | 35 | Male | Austin | 2005 | | Anna Karenina | 45 | Female | Stockholm | 1992 | | Anna Karenina | 45 | Female | Stockholm | 1998 | | Anna Karenina | 45 | Female | Stockholm | 2000 | | Anna Karenina | 45 | Female | Stockholm | 2010 | | Anna Karenina | 45 | Female | Moscow | 1998 | | Anna Karenina | 45 | Female | Moscow | 2001 | | Anna Karenina | 45 | Female | Moscow | 2005 | | Anna Karenina | 45 | Female | Austin | 1995 | | Anna Karenina | 45 | Female | Austin | 1999 | +---------------+-----+--------+-------------------+------------------------+
この例では、citiesLived.place は citiesLived_place に、citiesLived.yearsLived は citiesLived_yearsLived になりました。
BigQuery はネストされたフィールドを自動的にフラット化できますが、複数の繰り返しフィールドを処理するときは、明示的に FLATTEN を呼び出す必要があります。たとえば、次のようなレガシー SQL クエリを実行しようとするとします。
SELECT fullName, age FROM [dataset.tableId] WHERE (citiesLived.yearsLived > 1995 ) AND (children.age > 3)
BigQuery は次のようなエラーを返します。
Cannot query the cross product of repeated fields children.age and citiesLived.yearsLived
複数の繰り返しフィールドに対してクエリを実行するには、いずれかのフィールドをフラット化する必要があります。
SELECT fullName, age, gender, citiesLived.place FROM (FLATTEN([dataset.tableId], children)) WHERE (citiesLived.yearsLived > 1995) AND (children.age > 3) GROUP BY fullName, age, gender, citiesLived.place
この場合、次の結果が返されます。
+------------+-----+--------+-------------------+ | fullName | age | gender | citiesLived_place | +------------+-----+--------+-------------------+ | John Doe | 22 | Male | Stockholm | | Mike Jones | 35 | Male | Los Angeles | | Mike Jones | 35 | Male | Washington DC | | Mike Jones | 35 | Male | Portland | | Mike Jones | 35 | Male | Austin | +------------+-----+--------+-------------------+
WITHIN 句
WITHIN キーワードは特に集計関数と組み合わせてレコード内またはネストされたフィールド内の子や繰り返しフィールドを集計する場合に使用します。WITHIN キーワードを指定する場合、次のいずれかを使用して集計する範囲を指定する必要があります。
WITHIN RECORD: レコードに含まれる繰り返し値のデータを集計します。WITHIN node_name: 指定したノード内の繰り返し値のデータを集計します。ここで指定するノードは、集計関数内のフィールドの親ノードです。
上記の例で、それぞれの人の子供の数を調べるとします。それには、各レコードの children.name の数をカウントします。
SELECT fullName, COUNT(children.name) WITHIN RECORD AS numberOfChildren FROM [dataset.tableId];
次の結果が得られます。
+---------------+------------------+ | fullName | numberOfChildren | +---------------+------------------+ | John Doe | 2 | | Jane Austen | 2 | | Mike Jones | 3 | | Anna Karenina | 0 | +---------------+------------------+
比較のために、子供の名前をすべてリストしてみます。
SELECT fullName, children.name FROM [dataset.tableId]
+---------------+---------------+ | fullName | children_name | +---------------+---------------+ | John Doe | Jane | | John Doe | John | | Jane Austen | Josh | | Jane Austen | Jim | | Mike Jones | Earl | | Mike Jones | Sam | | Mike Jones | Kit | | Anna Karenina | None | +---------------+---------------+
これは WITHIN RECORD クエリの結果と一致します。John Doe には、Jane と John という名前の 2 人の子どもがいます。Jane Austen には、Josh と Jim という名前の 2 人の子供がいます。Mike Jones には、Earl、Sam、Kit という 3 人の子どもがいます。Anna Karenina には子どもがいません。
ここで、人々が住んだことがある場所の数を知りたいとします。この場合は、WITHIN 句を使用して 1 つの特定のノード全体で集計を実行します。
SELECT fullName, COUNT(citiesLived.place) WITHIN RECORD AS numberOfPlacesLived, citiesLived.place, COUNT(citiesLived.yearsLived) WITHIN citiesLived AS numberOfTimesInEachCity, FROM [dataset.tableId];
+---------------+---------------------+-------------------+-------------------------+ | fullName | numberOfPlacesLived | citiesLived_place | numberOfTimesInEachCity | +---------------+---------------------+-------------------+-------------------------+ | John Doe | 2 | Seattle | 1 | | John Doe | 2 | Stockholm | 1 | | Mike Jones | 4 | Los Angeles | 4 | | Mike Jones | 4 | Washington DC | 4 | | Mike Jones | 4 | Portland | 4 | | Mike Jones | 4 | Austin | 4 | | Anna Karenina | 3 | Stockholm | 4 | | Anna Karenina | 3 | Moscow | 3 | | Anna Karenina | 3 | Austin | 2 | +---------------+---------------------+-------------------+-------------------------+
このクエリは、次のことを行います。
citiesLived.placeでWITHIN RECORDを実行し、それぞれの人が住んだことのある場所の数をカウントします。citiesLived.yearsLivedでWITHINを実行し、それぞれの人が各都市に住んだことのある回数をカウントします（citiesLived全体でカウント）。
範囲を設定してネストされたフィールドや繰り返しフィールドを集計する機能は BigQuery の長所の 1 つであり、多くの場合、コストのかかる結合をクエリで使用する必要がなくなります。