BigQueryのUDFを使ってJSON Arrayのカラムをフラット化する

例えば以下のように店ごとのデータがJSONのArrayで保存されていた場合、それぞれの種類ごとに集計する場合には一度JSONのArrayを展開する必要があります。

store	price_data
a	[{kind: "orange", price: 100}, {"kind": "grape", price: 400}, {kind: "banana", price: 120}]
b	[{kind: "strawberry", price: 500}, {kind: "orange", price: 130}, {"kind": "grape", price: 400}, {kind: "banana", price: 150}]
c	[{"kind": "grape", price: 400}, {kind: "banana", price: 120}, {kind: "peach", price: 600}]

このような場合、以下のようにJSON Arrayの各行をemitするUDFを作成すると簡単に集計可能です。

UDF

function flatten_prices(row, emit) {
  var items = eval(row.price_data);
  items.forEach(function(item) {  // forEachで各行をオブジェクト化してemitする
    emit({store: row.store, kind: item.kind, price: item.price});
  });
}

bigquery.defineFunction(
  'flatten_prices',                     // UDF名
  ['store', 'price_data'],              // Inputされるデータ定義
  [{'name': 'shop', 'type': 'string'},  // Outputするカラム名とその型定義
   {'name': 'kind', 'type': 'string'},
   {'name': 'price', 'type': 'integer'}
   ],
  flatten_prices                        // データを処理する関数
);

Query

select
  kind,
  count(1),
  min(price),
  max(price),
  avg(price)
from
flatten_prices
(
select
  store,
  price_data
from [Path.to.table]
)
group by kind;

結果

Row	kind	f0_	f1_	f2_	f3_
1	orange	2	100	130	115.0
2	grape	3	400	400	400.0
3	banana	3	120	150	130.0
4	strawberry	1	500	500	500.0
5	peach	1	600	600	600.0

おわりに

不確定な要素についてJSONで保存しておくのは良いですが、データ量が増えるとそのまま課金額に響くのでご注意。