

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のように配列の数分だけ別のレコードになるように取り出すうまい方法がないだろうかと思い調べています。

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


CC BY-SA 3.0
2015-09-03T02:31:40.993
15170
2018-03-09T09:20:48.107
4377
question
google-bigquery
BigQueryでJSON文字列を保存して配列になっている値を集計したい場合のやり方


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

[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]


CC BY-SA 3.0
2015-09-03T09:23:03.830
15192
2015-09-03T09:23:03.830
4361
15170
answer


StandardSQL版を作成しました。  
Debug用にすごいばらしてあるけど、やってることは基本的に同じ。

[code]

    #standardSQL
    SELECT
      userno,
      JSON_EXTRACT_SCALAR(friend, "$.userno") AS friend_userno,
      JSON_EXTRACT_SCALAR(friend, "$.nickname") AS friend_nickname
    FROM (
      SELECT
        userno,
        CONCAT("{",REGEXP_REPLACE(friend, "^\\[\\{|\\}\\]$",""), "}") AS friend
      FROM (
        SELECT
          userno,
          friend
        FROM (
          SELECT
            userno,
            SPLIT( friends, "},{") AS friends
          FROM (
            SELECT
              JSON_EXTRACT('{"userno":12345,"friends":[{"userno":1, "nickname":"hogehoge"}, {"userno":1, "nickname":"fugafuga"}] }}', "$.userno") AS userno,
              JSON_EXTRACT('{"userno":12345,"friends":[{"userno":1, "nickname":"hogehoge"}, {"userno":1, "nickname":"fugafuga"}] }}', "$.friends") AS friends ))
        CROSS JOIN
          UNNEST(friends) AS friend))
    
[/code]


CC BY-SA 3.0
2018-03-09T08:37:42.680
42267
2018-03-09T08:37:42.680
4361
15170
answer


UDF で JSON.parse する手はどうでしょう。

[code]

    #standardSQL
    # STRUCT の型は JSON に合わせる
    CREATE TEMPORARY FUNCTION
      parse_logdata(logdata STRING)
      RETURNS STRUCT>>
      LANGUAGE js AS """
      return JSON.parse(logdata);
    """;
    # LOGDATA_TABLE にログが入っている前提
    WITH
      LOGDATA_TABLE AS (
      SELECT
        '{  "date": "2015-08-31",  "time": "00:00:00",  "type": "RESPONSE",  "userno": 12345,  "friends":[{"userno":1, "nickname":"hogehoge"}, {"userno":1, "nickname":"fugafuga"}]}' AS logdata
      UNION ALL
      SELECT
        '{  "date": "2015-08-31",  "time": "00:00:00",  "type": "RESPONSE",  "userno": 12346,  "friends":[{"userno":1, "nickname":"foobar"}, {"userno":1, "nickname":"piyopiyo"}]}')
    SELECT
      logdata.userno AS userno,
      friends.userno AS friend_no,
      nickname
    FROM (
      SELECT
        parse_logdata(logdata).*
      FROM
        LOGDATA_TABLE) AS logdata
    INNER JOIN
      UNNEST(logdata.friends) AS friends
    
[/code]

追記: UDF の入ったクエリの同時実行数には注意する必要があるかも。  
<https://cloud.google.com/bigquery/quotas?hl=en>  
<https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-
functions?hl=en#limits>  
また、 VIEW では使えないし BI ツールでも使えないのが普通ですね。


CC BY-SA 3.0
2018-03-09T09:03:08.917
42270
2018-03-09T09:20:48.107
2018-03-09T09:20:48.107
27680
27680
15170
answer