Slide 14
Slide 14 text
©MIXI
14
JSON型をSTRUCT型にしてわかりやすく
● 右下のようなクエリで実現
○ 詳細は「BigQueryのJSON型をSTRUCT型に変換する - Qiita(北島 2022)」
○ 数十種類のログに対して毎回このようなクエリを書きたくないので自動生成する
ログテーブル(app_log)
{“type”: “log_game_start”,
“user_id” : 394,
“time”: “2024-01-01 23:59:59”,
“payload”: {
“stage_id”: 1000,
“score”: 4567,
“players”: [123, 456, 7890]
}}
CREATE VIEW log_game_start AS
SELECT user_id, time,
SAFE.INT64(payload.stage_id) AS stage_id,
SAFE.INT64(payload.score) AS score,
ARRAY(SELECT SAFE.INT64(player) FROM
UNNEST(JSON_QUERY_ARRAY(payload.players))
player WITH OFFSET player_index ORDER BY
player_index) AS players
FROM app_log
WHERE type = “log_game_start”;