BigQuery入門！無料データでSQLのJSON関数をマスター

記事の対象者と解消できるお悩み

【対象者】

初中級データアナリスト

【興味関心】

1. Web APIやログデータの分析
2. JSONデータの扱い方
3. SQLスキルアップ
4. BigQueryの関数活用
5. 業務効率化

【解消できるお悩み】

1. Web APIから取得したJSONデータを分析したいけど、どうすればいいか分からない…
2. システムログなどのJSONデータから、必要な情報だけを抽出したい
3. BigQueryでJSONデータを扱う方法が知りたい
4. SQLでJSONデータを効率的に分析したい
5. データ分析をもっと効率化して、時間を節約したい

記事作成者

【人物】株式会社志庵　代表取締役　佐藤　光　起業6年目

【経歴】株式会社光通信➜光通信同期と起業➜データアナリスト転職➜SaaSで2度目起業

【会社実績】

SaaS 資金調達無しARR2億円

SaaS GMV200億円

【内容】自社AaaSであるStruccle、webスクレイピング、BigQueryに関する記事を作成

各種用語

• ARR(Annual Recurring Revenue)
  • サブスクリプションサービスの年間売上※当社はSaaS売上
• Struccle(ストラクル)
  • 株式会社志庵が独自開発しているAaaS
• AaaS(アース)
  • Data Analytics as a Serviceの意
• SaaS(サース)
  • Software as a Serviceの意
• GoogleCloudPlatform
  • Googleが提供するクラウドプラットフォーム。この中に様々なサービスが存在する。
• GCP(ジーシーピー)
  • GoogleCloudPlatformの略
• BigQuery(ビッグクエリ)
  • 上記GCPの中に含まれるデータ分析基盤サービス
• SQL(エスキューエル)
  • データベースを操作するプログラミング言語
• クエリ
  • SQLで記述した命令文
  • クエリ例）
    • SQL構文のクエリ⬇
      • SELECT item_name, price FROM sales_table where price >= 1000;
      • 上記クエリ(命令文)の意味としては「売上テーブル(sales_table)から価格(price)が1000円以上の商品名(item_name)と価格(price)を抽出する」
• ペタバイト
  • データの単位　ペタバイト＝1000テラバイト＝約100兆行のデータ

概要

Web APIやアプリケーションログなど、多くのデータがJSON形式でやり取りされています。Google CloudのBigQueryでは、JSON形式のデータを効率的に扱うための様々なJSON関数が用意されています。これらの関数を使いこなすことで、JSONデータから必要な情報を抽出したり、加工したりすることが容易になります。

この記事では、BigQueryで利用できる基本的なJSON関数を、分かりやすい例とともに紹介します。BigQueryの無料公開データセットを使って、実際に手を動かしながらJSON関数をマスターしましょう！

目次

1. BigQueryのJSON関数とは？
2. BigQueryの基本JSON関数一覧
   1. JSON_EXTRACT
   2. JSON_EXTRACT_SCALAR
   3. JSON_QUERY
   4. JSON_VALUE
   5. JSON_EXTRACT_ARRAY
   6. JSON_QUERY_ARRAY
   7. TO_JSON_STRING
3. FAQ：BigQueryに関するよくある質問
4. まとめ：BigQueryのJSON関数をマスターして、JSONデータを自在に操ろう！

1. BigQueryのJSON関数とは？

BigQueryのJSON関数は、JSON（JavaScript Object Notation）形式のデータを扱うための関数です。JSONは、データの構造を表すために広く使われているテキストベースのデータ形式です。BigQueryのJSON関数は、JSONデータから特定の値を抽出したり、JSONデータを変換したりする際に役立ちます。

2. BigQueryの基本JSON関数一覧

BigQueryで利用できる基本的なJSON関数を、例とともに紹介します。

2.1 JSON_EXTRACT

JSON文字列から、指定されたJSONPathに対応するJSON fragmentを抽出します。抽出されたfragmentはJSON文字列として返されます。

構文：

JSON_EXTRACT(json_string_expr, json_path_string_literal)

例： JSON文字列 ‘{“name”: “John”, “age”: 30}’ から、キー ‘name’ に対応する値を抽出する

SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') AS name;

2.2 JSON_EXTRACT_SCALAR

JSON文字列から、指定されたJSONPathに対応するスカラー値を抽出します。抽出された値はSQLのデータ型に変換されます。

構文：

JSON_EXTRACT_SCALAR(json_string_expr, json_path_string_literal)

例： JSON文字列 ‘{“name”: “John”, “age”: 30}’ から、キー ‘age’ に対応する値を整数値として抽出する

SELECT JSON_EXTRACT_SCALAR('{"name": "John", "age": 30}', '$.age') AS age;

2.3 JSON_QUERY

JSON文字列から、指定されたJSONPathに対応するJSON fragmentを抽出します。抽出されたfragmentはJSON文字列として返されます。JSON_EXTRACTとの違いは、JSON_QUERYはより複雑なJSONPath式をサポートしている点です。

構文：

JSON_QUERY(json_string_expr, json_path_string_literal)

例： JSON文字列 ‘{“person”: {“name”: “John”, “address”: {“city”: “New York”}}}’ から、キー ‘city’ に対応する値を抽出する

SELECT JSON_QUERY('{"person": {"name": "John", "address": {"city": "New York"}}}', '$.person.address.city') AS city;

2.4 JSON_VALUE

JSON文字列から、指定されたJSONPathに対応するスカラー値を抽出します。抽出された値はSQLのデータ型に変換されます。JSON_EXTRACT_SCALARとの違いは、JSON_VALUEはエラー処理の方法が異なる点です。JSON_VALUEは、指定されたJSONPathに対応する値が見つからない場合、NULLを返します。

構文：

JSON_VALUE(json_string_expr, json_path_string_literal)

例： JSON文字列 ‘{“name”: “John”, “age”: 30}’ から、キー ‘phone’ に対応する値を抽出する（キーが存在しないためNULLが返る）

SELECT JSON_VALUE('{"name": "John", "age": 30, "phone": "iPhone"}', '$.phone') AS phone;

2.5 JSON_EXTRACT_ARRAY

JSON文字列から、指定されたJSONPathに対応するJSON配列を抽出します。抽出された配列は、ARRAY型の値として返されます。

構文：

JSON_EXTRACT_ARRAY(json_string_expr, json_path_string_literal)

例： JSON文字列 ‘{“fruits”: [“apple”, “banana”, “orange”]}’ から、キー ‘fruits’ に対応する配列を抽出する

SELECT JSON_EXTRACT_ARRAY('{"fruits": ["apple", "banana", "orange"]}', '$.fruits') AS fruits;

2.6 JSON_QUERY_ARRAY

JSON文字列から、指定されたJSONPathに対応するJSON配列を抽出します。抽出された配列は、ARRAY型の値として返されます。JSON_EXTRACT_ARRAYとの違いは、JSON_QUERY_ARRAYはより複雑なJSONPath式をサポートしている点です。

構文：

JSON_QUERY_ARRAY(json_string_expr, json_path_string_literal)

例： JSON文字列 ‘{“person”: {“name”: “John”, “hobbies”: [“reading”, “music”, “sports”]}}’ から、キー ‘hobbies’ に対応する配列を抽出する

SELECT JSON_QUERY_ARRAY('{"person": {"name": "John", "hobbies": ["reading", "music", "sports"]}}', '$.person.hobbies') AS hobbies;

2.7 TO_JSON_STRING

SQL値をJSON文字列に変換します。

構文：

TO_JSON_STRING(sql_value[, string_format])

例： 数値 123 をJSON文字列に変換する

SELECT TO_JSON_STRING(123) AS json_string;

4. FAQ：BigQueryに関するよくある質問

Q1. BigQueryの無料枠を超えて利用すると、どのくらい費用がかかりますか？

A1. 無料枠を超えた場合、従量課金制で料金が発生します。料金は、処理したデータ量やストレージ容量などによって異なります。詳しくは、BigQueryの料金ページをご確認ください。

Q2. BigQueryで使えるSQLは、他のデータベースと同じですか？

A2. BigQueryは標準SQLをサポートしており、他のデータベースで学んだSQLの知識を活かすことができます。ただし、BigQuery独自の関数や構文も存在するため、BigQueryのドキュメントで詳細を確認することをおすすめします。

Q3. SQLの学習には、どのような教材がありますか？

A3. SQLの学習には、オンライン学習サイト、書籍、動画教材など、様々な教材があります。初心者向けの教材から、上級者向けの教材まで、レベルに合わせて選ぶことができます。Googleが提供するBigQueryのチュートリアルもSQLの学習に役立ちます。

5. まとめ：BigQueryのJSON関数をマスターして、JSONデータを自在に操ろう！

BigQueryのJSON関数は、JSONデータを扱うデータ分析に非常に役立ちます。この記事で紹介した関数以外にも、様々なJSON関数が用意されています。BigQueryの公式リファレンスなどを参考に、JSON関数をマスターし、JSONデータを自在に操れるようになりましょう！

BigQuery導入サポート、転職検討中の方はお問い合わせフォームからご相談くださいませ。

誠心誠意精一杯対応いたします。

【参考URL】

GoogleCloudPlatform：https://console.cloud.google.com/welcome/new

BigQuery：https://cloud.google.com/bigquery?hl=ja

BigQueryリリース情報：https://cloud.google.com/bigquery/docs/release-notes

BigQuery料金：https://cloud.google.com/bigquery/pricing

BigQuery料金無料枠：https://cloud.google.com/bigquery/pricing?hl=ja#free-tier

Google Cloud活用事例：https://cloud.google.com/customers/index.html?hl=ja#

Looker Studio：https://cloud.google.com/looker-studio?hl=ja

Looker Studioサンプル：https://cloud.google.com/bigquery/docs/visualize-looker-studio?hl=ja