BigQueryでJSON文字列を保存して配列になっている値を集計したい場合のやり方


Qiitaの以下のエントリをみて、これができるとログの解析が捗りそうと思い色々試してみています。  
<http://qiita.com/hakobera/items/5e280ad33d72f82de39c>

JSON_EXTRACT,
JSON_EXTRACT_SCALARを使えば値を取り出せるのでこれで集計用のテーブルを作れば要件満たせそうと思っていたのですが、保存されているJSONデータに配列がありました。

たとえば以下のような感じのログデータがBigQueryのテーブルのlogdataというカラムに入っているとして、

[code]

    {
      "date": "2015-08-31",
      "time": "00:00:00",
      "type": "RESPONSE",
      "userno": 12345,
      "friends":[{"userno":1, "nickname":"hogehoge"}, {"userno":1, "nickname":"fugafuga"}]
    }
    
[/code]

最悪は、

[code]

    select 
      JSON_EXTRACT_SCALAR(logdata, '$.friends[0].userno') friend0,
      JSON_EXTRACT_SCALAR(logdata, '$.friends[1].userno') friend1
    from serverlog_20150831
    
[/code]

のようにして配列の数だけ並べたらいいのかもしれませんが  
FLATTENのように配列の数分だけ別のレコードになるように取り出すうまい方法がないだろうかと思い調べています。

どなたか良いアイディアがありましたらご教示ください。




がんばってやってみました！

[code]

    SELECT
      JSON_EXTRACT (logdata, '$.userno') AS userno,
      "{"+ REGEXP_REPLACE ( SPLIT ( JSON_EXTRACT (json, '$.friends'), "},{"), "^\\[\\{|\\}\\]$","") + "}" AS friend
    FROM
      serverlog_20150831
    
[/code]

これの結果が以下です。

[code]

    Row userno  friend   
    1   12345   {"userno":1,"nickname":"hogehoge"}   
    2   12345   {"userno":1,"nickname":"fugafuga"}
    
[/code]

そして、更にここからjsonをばらしていって

[code]

    SELECT
      JSON_EXTRACT (logdata, '$.userno') AS userno,
      JSON_EXTRACT("{"+ REGEXP_REPLACE ( SPLIT ( JSON_EXTRACT (logdata, '$.friends'), "},{"), "^\\[\\{|\\}\\]$","") + "}",'$.userno') AS friend.userno,
      SUBSTR(JSON_EXTRACT("{"+ REGEXP_REPLACE ( SPLIT ( JSON_EXTRACT (logdata, '$.friends'), "},{"), "^\\[\\{|\\}\\]$","") + "}",'$.nickname'), 2,LENGTH(JSON_EXTRACT("{"+ REGEXP_REPLACE ( SPLIT ( JSON_EXTRACT (logdata, '$.friends'), "},{"), "^\\[\\{|\\}\\]$","") + "}",'$.nickname')) -2) AS friend.nickname
    FROM
      serverlog_20150831
    
[/code]

とすると、以下になります。

[code]

    Row userno  friend_userno   friend_nickname  
    1   12345   1               hogehoge     
    2   12345   1               fugafuga
    
[/code]

