がんばってやってみました! SELECT JSON_EXTRACT (logdata, '$.userno') AS userno, "{"+ REGEXP_REPLACE ( SPLIT ( JSON_EXTRACT (json, '$.friends'), "},{"), "^\\[\\{|\\}\\]$","") + "}" AS friend FROM serverlog_20150831 これの結果が以下です。 Row userno friend 1 12345 {"userno":1,"nickname":"hogehoge"} 2 12345 {"userno":1,"nickname":"fugafuga"} そして、更にここからjsonをばらしていって SELECT JSON_EXTRACT (logdata, '$.userno') AS userno, JSON_EX
![BigQueryでJSON文字列を保存して配列になっている値を集計したい場合のやり方](https://cdn-ak-scissors.b.st-hatena.com/image/square/f6bc393cd0b6bd2196935e603c5d031bd6ca6699/height=288;version=1;width=512/https%3A%2F%2Fcdn.sstatic.net%2FSites%2Fja%2FImg%2Fapple-touch-icon%402.png%3Fv%3D1cfb10b305ca)