「JSON」の基本と「JSON_EXTRACT」の基本

■JSONの基本

・基本（文字列と数値）

{
"name": "Tanaka",
"age": 26
}

※Keyは（値がNULLに関わらず）、全てで存在しない場合がある。

・配列

{
"color_list": [ "red", "green", "blue" ],
"num_list": [ 123, 456, 789 ]
}

・オブジェクト

{
"user_info": {
             "user_id": "A1234567",
             "user_name": "Yamada Taro"
             }
}

■JSON_EXTRACTの基本

JSON

Studentという列が下記の場合

{"name": "Susan Smith",
"org": "engineering",
"projects":[
           {"name":"project1", "completed":false},
           {"name":"project2", "completed":true}
           ]
}

・基本構文1

JSON_EXTRACT(列名, '$') AS 新列名

・基本構文2

JSON_EXTRACT(列名, '$.KEY名') AS 新列名

・基本例文1

JSON_EXTRACT(Student, '$') AS 新列名

結果

第一階層レベルで取り出せると思われる。

・基本構文2

JSON_EXTRACT(Student, '$.name') AS name

結果

+----------------+
| name           |
+-----------------
| "Susan Smith"  |
+----------------+

JSON_EXTRACT_SCALAR

""を省く

・応用構文1　JSONの中身が、[で始まり、]で終わっている場合

JSON_EXTRACT(列名, '$[0].key名') AS 新列名

※$の後に.は要らない
参考：
AWS　JSON
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/extracting-data-from-JSON.html

GCP　JSON
https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators?hl=ja

Google Cloud Platform 実践ビッグデータ分析基盤開発 ストーリーで学ぶ Google BigQuery [ 株式会社トップゲート ]