BigQueryでJSON文字列を保存して配列になっている値を集計したい場合のやり方
Qiitaの以下のエントリをみて、これができるとログの解析が捗りそうと思い色々試してみています。
http://qiita.com/hakobera/items/5e280ad33d72f82de39c
JSON_EXTRACT, JSON_EXTRACT_SCALARを使えば値を取り出せるのでこれで集計用のテーブルを作れば要件満たせそうと思っていたのですが、保存されているJSONデータに配列がありました。
たとえば以下のような感じのログデータがBigQueryのテーブルのlogdataというカラムに入っているとして、
{
"date": "2015-08-31",
"time": "00:00:00",
"type": "RESPONSE",
"userno": 12345,
"friends":[{"userno":1, "nickname":"hogehoge"}, {"userno":1, "nickname":"fugafuga"}]
}
最悪は、
select
JSON_EXTRACT_SCALAR(logdata, '$.friends[0].userno') friend0,
JSON_EXTRACT_SCALAR(logdata, '$.friends[1].userno') friend1
from serverlog_20150831
のようにして配列の数だけ並べたらいいのかもしれませんが
FLATTENのように配列の数分だけ別のレコードになるように取り出すうまい方法がないだろうかと思い調べています。
どなたか良いアイディアがありましたらご教示ください。