BigQueryでJSONをテーブルに自動変換できるようにしてみた

こちらはエムスリー Advent Calendar 2024の20日目の記事です。

デジスマチームの田口です。 デジスマ診療（以降デジスマ）はQRコードによるチェックインや自動後払い、オンライン診療など新しい医療体験を提供するサービスです。

www.youtube.com

ありがたいことにサービスは成長し続けており、これに伴ってKPIの深掘りや施策の効果検証のためのデータ分析もより積極的に行われるようになってきました。

デジスマチームではデータ分析基盤にBigQueryを利用しており、Amazon Auroraや各種ログデータをBigQueryに連携し、様々な分析をしています。 多種多様なデータを連携している都合上、データの中にはJSON形式のカラムのものもあり、この内容を検索や集計したいケースも出てきます。 分析する中でJSON形式のデータをテーブルに変換したくなったため、JSONのカラムをテーブルに変換する方法を考えてみました。

はじめに

デジスマでBigQueryに連携しているデータの中には、構造的なデータや配列を表現したいものをJSON形式で保存しているものもあります。 これはデータ保存の観点では柔軟ですが、それ故にSQLで分析する際は工夫が必要です。

サンプルデータ

本記事では、こちらの 1.7m users (504.3 MB) データをサンプルとして利用します。

Exploring the power of JSON: a real-life JSON file example collection

[
  {
    "id": 0,
    "name": "Elijah",
    "city": "Austin",
    "age": 78,
    "friends": [
      {
        "name": "Michelle",
        "hobbies": [
          "Watching Sports",
          "Reading",
          "Skiing & Snowboarding"
        ]
      },
      {
        "name": "Robert",
        "hobbies": [
          "Traveling",
          "Video Games"
        ]
      }
    ]
  },
  {
    "id": 1,
    "name": "Noah",
    "city": "Boston",
    "age": 97,
    "friends": [
      {
        "name": "Oliver",
        "hobbies": [
          "Watching Sports",
          "Skiing & Snowboarding",
          "Collecting"
        ]
      },
      ...
    ]
  },
  ...
]

サンプルのデータは上記のような構造になっています。 BigQueryには、上記データを1つずつ user_json カラムに格納した users テーブルを作成します。

カラム名	データ型	値の例
user_json	JSON	{"id":0,"name":"Elijah","city":"Austin","age":78,...}

JSON関数を用いた分析

BigQueryではJSON型をサポートしており、JSONを操作する関数も様々なものが提供されています。

例えば、年齢が50歳以上のユーザーを取得するには次のようなクエリになります。

SELECT
  JSON_VALUE(user_json, '$.id') AS id,
  JSON_VALUE(user_json, '$.name') AS name,
  JSON_VALUE(user_json, '$.age') AS age,
FROM
  test.users -- `test` データセットにテーブルを作成
WHERE
  CAST(JSON_VALUE(user_json, '$.age') AS int64 ) >= 50
ORDER BY CAST(id AS int64)

このクエリの結果（一部）は次のようになります。

JSON_VALUE関数を用いることで、JSONの値から特定のフィールドの値を抽出できます。 フィールドはJSONPath形式で指定します。

このようにJSON関数を用いることで、JSONのフィールドで分析できます。

JSONをテーブルに変換

JSON関数によってある程度JSONフィールドの値を利用した分析が可能になりますが、別のテーブルとjoinする場合など、辛いケースが出てきます。 そこで、JSONフィールドをフラットなテーブルで表現できないか考えます。

SELECT
  JSON_VALUE(user_json, '$.id') AS id,
  JSON_VALUE(user_json, '$.name') AS name,
  JSON_VALUE(user_json, '$.age') AS age,
  JSON_VALUE(user_json, '$. friends.name') AS friend_names,
  ...
FROM
  test.users

上記のようにフィールドを1つずつ指定することによってフラットな形式に変換することはできますが、フィールド数が多い場合や構造が複雑な場合は指定が手間になります。またJSONの構造が変わった時に変換のクエリも修正する必要があります。

そこで、JSONの実データからスキーマを自動的に抽出し、フラットなテーブルにするSQLを考えました。

-- 1. UDF: JSONキーと値を同時に取得
CREATE TEMP FUNCTION extract_keys_and_values(input STRING)
RETURNS ARRAY<STRUCT<key STRING, value STRING>>
LANGUAGE js AS """
  function flattenObj(obj, parent = '', res = []) {
    for (let key in obj) {
      const propName = parent ? parent + '.' + key : key;
      if (typeof obj[key] === 'object' && obj[key] !== null) {
        flattenObj(obj[key], propName, res);
      } else {
        res.push({ key: propName, value: String(obj[key]) });
      }
    }
    return res;
  }
  return flattenObj(JSON.parse(input));
""";

-- 2. 一時テーブルの作成: JSONをキーと値のペアに展開
CREATE TEMP TABLE temp_table AS
SELECT
  row_num,
  kv.key,
  kv.value
FROM
  (
    SELECT *,
    ROW_NUMBER() OVER () AS row_num,
    FROM test.users
  ),
  UNNEST(extract_keys_and_values(TO_JSON_STRING(user_json))) AS kv;

-- 3. ピボットクエリの動的生成
EXECUTE IMMEDIATE (
  SELECT FORMAT("""
    SELECT * EXCEPT(row_num)
    FROM temp_table
    PIVOT (
      ANY_VALUE(value) 
      FOR REPLACE(key, '.', '__') IN (%s)
    )
    ORDER BY CAST(id AS INT64)
  """, STRING_AGG(FORMAT('"%s"', REPLACE(key, '.', '__')), ','))
  FROM (
    SELECT DISTINCT key FROM temp_table
  )
);

このようなSQLを実行することによって、user_json を次のようなテーブルに変換できます。 このSQLについて詳しく見ていきます。

1. UDF: JSONキーと値を同時に取得

はじめに、JSONのキーと値のペアを出力する関数を作成します。 BigQueryではユーザー定義の関数を作成することができ、SQLまたはJavaScriptコードで処理を定義できます。 ここでは、JSON文字列を入力とし、キーと値のペアを出力します。ネストされたキーは parent.child 形式に変換します。また、値はすべてSTRING型で返却されます。

[
  { key: "id", value: "0" },
  { key: "name", value: "Elijah" },
  { key: "age", value: "78" },
  { key: "friends.0.name": "Michelle" },
  { key: "friends.0.hobbies.0": "Watching Sports" },
  ...
]

2. 一時テーブルの作成: JSONをキーと値のペアに展開

次に、この関数を利用してキーと値のペアに展開し、一時テーブルに保存します。 一時テーブルの構造は次のようになります。

row_num	key	value
1	id	0
1	name	Elijah
1	age	78
1	friends.0.name	Michelle
1	friends.0.hobbies.0	Watching Sports
...
2	id	1
2	name	Noah
2	age	97
2	friends.0.name	Oliver
2	friends.0.hobbies.0	Watching Sports

後で各ユーザー毎にグルーピングできるよう一意になる値を持っておく必要があります。今回は ROW_NUMBER 関数で行番号を付与するようにしました。

3. ピボットクエリの動的生成

最後に、2.で作成した一時テーブルについて、キー毎に列を作成するピボットテーブルを動的に生成します。 SELECT DISTINCT key FROM temp_table でJSONのキーの一覧を取得し、これをピボットの列として指定します。 また、EXECUTE IMMEDIATEを利用し、IN句の条件を動的に組み立てています。 REPLACE(key, '.', '__') は、テーブルの列名に . が使えないためアンダースコア __ に書き換えています。

このようなクエリを実行することで、最終的にJSONデータをフラット化したテーブルを出力することができます。

id	name	age	friends__0__name	friends__0__hobbies__0	...
0	Elijah	78	Michelle	Watching Sports	...
1	Noah	97	Oliver	Watching Sports	...

おわりに

BigQueryのJSONカラムに対して、JSON関数で分析する方法や、フラットなテーブルとして出力する方法を紹介しました。

ここまで書いておいてになりますが、BigQueryでは公式でファイルからJSONデータを読み込む方法が提供されているため、データをBigQueryに連携する前に取得できるのであれば、このような機能を利用した方が吉です。 独自のクエリをメンテナンスする必要もなく、また今回のようなSQLはJSONカラムをスキャンするのにコストがかかるため、奥の手として使うのが良いでしょう。

We are hiring!!

エムスリーでは絶賛エンジニアを募集中です！ デジスマ診療以外にも様々なプロダクトがありますので、ご興味ある方は是非カジュアル面談等ご応募ください！

jobs.m3.com