BigQueryでJSON型の文字列の取得

はじめまして!
クラウドエンジニアのkitoketaです。

BigQueryでJSONデータを抽出する関数について、基本的な書き方と、便利な使い方をまとめました。生データのJSONをBigQueryで展開してから分析することも多いので、覚えておいておくと便利です!

■公式ドキュメント
https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions?hl=ja

JSON_EXTRACT

---

JSONデータをパースしてSTRING値を返します。

すべての要素取得

SELECT JSON_EXTRACT('{"name" : "Bob", "age" : 15}', '$') AS json_string 

+-----------------------+
| json_string           |
+-----------------------+
| {"name":"Bob","age":15}   |
+-----------------------+

要素を指定して取得

SELECT
 JSON_EXTRACT('{"name" : "Bob", "age" : 15}', '$.name') AS name,
 JSON_EXTRACT('{"name" : "Bob", "age" : 15}', '$.age') AS age

+------+-----+
| name | age |
+------+-----+
| "Bob" | 15  |
+------+-----+

リストの要素取得

WITH tmp AS (
SELECT
 '{"name" : "Bob", "age" : 15, "family" : [{"name" : "Tom", "age" : 40}, {"name" : "Emma", "age" : 39}]}' AS json
)
SELECT
 JSON_EXTRACT(json, '$.family') AS family,
 JSON_EXTRACT(json, '$.family[0]') AS first_family,
 JSON_EXTRACT(json, '$.family[0].name') AS first_family_name
FROM tmp

+----------------------------------------------------+-------------------------+-------------------+
| family                                             | first_family            | first_family_name |
+----------------------------------------------------+-------------------------+-------------------+
| [{"name":"Tom","age":40},{"name":"Emma","age":39}] | {"name":"Tom","age":40} | "Tom"             |
+----------------------------------------------------+-------------------------+-------------------+

JSON_EXTRACT_SCALAR

文字列のダブルクォーテーションを外した値を取得する

SELECT
 JSON_EXTRACT('{"name" : "Bob", "age" : 15}', '$.name') AS name,
 JSON_EXTRACT('{"name" : "Bob", "age" : 15}', '$.age') AS age

+------+-----+
| name | age |
+------+-----+
| Bob   | 15  |
+------+-----+