【BigQuery演習】JSON文字列をSQLで展開してみよう！【JSON関数】

【BigQuery演習】JSON文字列をSQLで展開してみよう！

おはようございます。本日のBigQuery演習のテーマはこちら！

JSON文字列をSQLで展開してみよう！

BigQueryはJSONをテーブルにロードする機能がありますが、JSONを文字列としてロードして、SQLで加工することもできます！

例えば以下のようなJSON文字列のデータがあった時、SQLで加工して人が扱える形にできると便利ですよね。

f:id:true-fly:20210822195840p:plain

今日はBigQueryのJSON関数を使った問題です！

ルール

今回は、以前紹介しました、GCPのリリースノートテーブルのJSONスキーマを文字列として読み込み、それをSQLで読める形に加工しよう。データは以下の「ベースとなるSQL」のWITH句に記述すること
JSON関数を使って、name, type, modeが列になるように加工すること
最終的に出力されるレコードは6レコードであること

ベースとなるSQL

WITH json_str AS (
    SELECT '[{"name":"description","type":"STRING","mode":"NULLABLE"},{"name":"release_note_type","type":"STRING","mode":"NULLABLE"},{"name":"published_at","type":"DATE","mode":"NULLABLE"},{"name":"product_id","type":"INTEGER","mode":"NULLABLE"},{"name":"product_name","type":"STRING","mode":"NULLABLE"},{"name":"product_version_name","type":"STRING","mode":"NULLABLE"}]' AS value

)
-- データは以下のbqコマンドで取得したものを利用しています。
-- bq show  --schema --format=json bigquery-public-data:google_cloud_release_notes.release_notes

GCPのリリースノートテーブルの使い方を解説した記事はこちら！ www.true-fly.com

BigQuerySQLを学ぶのであれば以下の書籍がオススメ！

集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析できるDigital Camp

作者:木田和廣
インプレス

Amazon

答え

WITH json_str AS (
    SELECT '[{"name":"description","type":"STRING","mode":"NULLABLE"},{"name":"release_note_type","type":"STRING","mode":"NULLABLE"},{"name":"published_at","type":"DATE","mode":"NULLABLE"},{"name":"product_id","type":"INTEGER","mode":"NULLABLE"},{"name":"product_name","type":"STRING","mode":"NULLABLE"},{"name":"product_version_name","type":"STRING","mode":"NULLABLE"}]' AS value
),
json_array AS (
    SELECT 
        JSON_EXTRACT_ARRAY(value, '$.') AS value
    FROM 
        json_str
)
SELECT 
    JSON_EXTRACT_SCALAR(unnested_value, '$.name') AS name,
    JSON_EXTRACT_SCALAR(unnested_value, '$.type') AS type,
    JSON_EXTRACT_SCALAR(unnested_value, '$.mode') AS mode,
FROM
    json_array,UNNEST(value) unnested_value

解説

まずはJSON文字列が1レコードになっているので、これを配列に展開します。
JSON文字列を配列に展開するのに、JSON_EXTRACT_ARRAY(JSON文字列, JSONパス)を実行しています。
$.という記述のJSONパスは、ルートパスを示しています。
[レコード1,レコード2,レコード3]というようなJSON文字列があったら、