MySQL道普請便り

第249回MySQLに格納した複雑なJSONから値を検索する

MySQLのJSON型は、柔軟なデータ構造をそのまま格納できる点で非常に便利です。ユーザー設定やタグ情報、外部APIのレスポンスなど、テーブル構造に縛られずに情報を保存したい場面では重宝します。

ただし、複雑なJSONデータをそのまま詰め込んだ場合には注意が必要です。たとえばSHOW CREATE TABLEを見てもJSONカラムの中身までは確認できないため、⁠このキーって本当に全件にあるの?」⁠どんな値が入っているのか調べたい」といった悩みが出てくることがあります。

そんなときに頼りになるのが、MySQLが提供する各種JSON関数です。構造の把握や値の抽出、特定の値の有無の確認など、うまく使えばJSONの中身とつき合いやすくなります。

本稿では、いくつかの代表的なJSON関数をサンプルデータとともに紹介しながら、その使いどころや注意点を実践的に紹介していきます。なお、今回の検証に使用しているMySQLのバージョンは8.4.4です。

サンプルデータの準備

検証しやすいように、3人のユーザーを用いたサンプルデータを用意します。usersテーブルには、各ユーザーの属性情報をJSON型で格納します。

今回のデータはすべて同じ形式ではなく、たとえばaddressキーの要素はユーザーごとに異なる構造になっています。これは、柔軟なJSONデータを使う現場でよくある状況です。

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  profile JSON
);

INSERT INTO users (id, name, profile) VALUES
(1, '佐藤', JSON_OBJECT(
  'tags', JSON_ARRAY('在宅勤務', 'バックエンド', 'Go言語'),
  'attributes', JSON_OBJECT('job_title', 'エンジニア', 'department', '技術部', 'employment_type', '正社員'),
  'settings', JSON_ARRAY(
    JSON_OBJECT('timestamp', '2024-01-01', 'notifications', true),
    JSON_OBJECT('timestamp', '2024-06-01', 'notifications', false)
  ),
  'address', JSON_OBJECT('prefecture', '東京都', 'postal_code', '100-0001')
)),
(2, '鈴木', JSON_OBJECT(
  'tags', JSON_ARRAY('在宅勤務', 'フロントエンド', 'Vue.js'),
  'attributes', JSON_OBJECT('job_title', 'デザイナー', 'department', 'デザイン部', 'employment_type', '正社員'),
  'settings', JSON_ARRAY(),
  'address', JSON_OBJECT('postal_code', '530-0001')
)),
(3, '田中', JSON_OBJECT(
  'tags', JSON_ARRAY('在宅勤務', 'インフラ', 'Go言語', 'エンジニア', '正社員'),
  'attributes', JSON_OBJECT('job_title', 'インフラエンジニア', 'department', '技術部', 'employment_type', '正社員'),
  'settings', JSON_ARRAY(
    JSON_OBJECT('timestamp', '2024-03-15', 'notifications', true)
  ),
  'address', JSON_OBJECT('prefecture', '愛知県')
));

このデータをベースに、次の「JSONデータの検索する関数」セクションで各関数の使い方を検証していきます。

JSONデータの検索をする関数

それでは、先ほど用意したサンプルデータを使って、MySQLでJSONデータを扱う関数の動作を順番に確認していきましょう。

JSON_CONTAINS ―完全一致で検索

JSON_CONTAINSは、指定したJSON値が対象のJSONデータの中に完全に含まれているかをチェックする関数です。型・構造・配列の順序なども一致している必要があり、曖昧な条件には向いていません。

その一方で、⁠このタグが確実に入っている人だけを取り出したい」⁠この設定を持っているユーザーだけ抽出したい」といった厳密な一致条件での検索にはぴったりです。

mysql> SELECT name FROM users
    -> WHERE JSON_CONTAINS(profile->'$.tags', JSON_ARRAY('在宅勤務'));
+--------+
| name   |
+--------+
| 佐藤   |
| 鈴木   |
| 田中   |
+--------+
3 rows in set (0.00 sec)

JSON_OVERLAPS ―タグ検索等に便利

JSON_OVERLAPSは、2つのJSON値の間に共通する要素(重なり)があるかを確認する関数です。配列同士だけでなくオブジェクト同士でも使用できますが、実務ではタグやカテゴリのような配列に対して使う場面が特に多いです。

「完全一致じゃなくても、どれか1つでも含まれていればOK」というような、ゆるやかなフィルタに向いています。

mysql> SELECT name FROM users
    -> WHERE JSON_OVERLAPS(profile->'$.tags', JSON_ARRAY('Go言語', 'フロントエンド'));
+--------+
| name   |
+--------+
| 佐藤   |
| 鈴木   |
| 田中   |
+--------+
3 rows in set (0.00 sec)

この例では、3人のユーザーのいずれもtagsに「Go言語」または「フロントエンド」のいずれかを含んでいるため、全件がマッチしています。

JSON_OVERLAPSとJSON_CONTAINSはどちらも「一致する値があるか」を調べる点では似ていますが、特に、第2引数にJSON配列を渡した場合、両者の意味合いは大きく異なります。

  • JSON_OVERLAPSはOR条件(1つでも一致すればOK)
  • JSON_CONTAINSはAND条件(すべて含んでいなければNG)

たとえば、JSON_ARRAY('A', 'B')を第2引数に渡した場合、JSON_CONTAINSでは両方の要素が対象に含まれている必要がありますが、JSON_OVERLAPSではどちらか1つでも含まれていればOKです。

JSON_VALUE ―スカラー値の抽出

JSON_VALUEは、JSONドキュメントから値を抽出して指定された型に変換する関数です。型の指定がない場合はVARCHAR(512)で返します。明示的に文字列や数値として取り出したいときや、SELECT文でJSONカラムの中身を列として表示したいときに便利です。

mysql> SELECT name, JSON_VALUE(profile, '$.attributes.job_title') AS job FROM users;
+--------+-----------------+
| name   | job             |
+--------+-----------------+
| 佐藤   | エンジニア      |
| 鈴木   | デザイナー      |
| 田中   | エンジニア      |
+--------+-----------------+
3 rows in set (0.00 sec)

似た関数にJSON_EXTRACTがありますが、CAST( JSON_UNQUOTE( JSON_EXTRACT(json_doc, path) ) AS type)と同等の関数になります。

JSON_KEYS ―属性の一覧を取得したいとき

JSON_KEYSは、指定したJSONオブジェクトのトップレベルのキーを配列で返す関数です。JSON配列に対して使うとNULLが返るため注意が必要です。

設定項目やユーザー属性などが柔軟に増えるようなケースで、⁠今どんなキーが使われているか」を把握するのに役立ちます。

mysql> SELECT name, JSON_KEYS(profile, '$.attributes') AS attribute_keys FROM users;
+--------+------------------------------------------------+
| name   | attribute_keys                                 |
+--------+------------------------------------------------+
| 佐藤   | ["job_title", "department", "employment_type"] |
| 鈴木   | ["job_title", "department", "employment_type"] |
| 田中   | ["job_title", "department", "employment_type"] |
+--------+------------------------------------------------+
3 rows in set (0.00 sec)

このように、各ユーザーのprofileのattributesに含まれる項目名を一覧で取得できます。

JSON_CONTAINS_PATH ―構造の存在チェック

JSON_CONTAINS_PATHは、指定したJSONパスが存在するかどうかをチェックする関数です。

第2引数にはone(いずれか1つでも存在すればOK)またはall(すべてのパスが存在している必要あり)を指定します。

JSON_CONTAINS_PATH(json_doc, 'one' or 'all', path1, path2, ...)
oneallの違い
  • one:パスのうちいずれか1つでも存在すればTRUEを返す
  • all:すべてのパスが存在していないとFALSEを返す

構造が柔軟なJSONカラムを扱う場面で、⁠このフィールドが存在しているレコードだけを処理したい」といった用途に非常に便利です。

以下は、profileカラムの中のaddress.postal_codeとaddress.prefectureの存在チェックを行う例です。

postal_codeが存在するユーザー(1人目と2人目)
mysql> SELECT name FROM users WHERE JSON_CONTAINS_PATH(profile, 'one', '$.address.postal_code');
+--------+
| name   |
+--------+
| 佐藤   |
| 鈴木   |
+--------+
2 rows in set (0.00 sec)
postal_codeかprefectureのどちらかが存在するユーザー(全員)
mysql> SELECT name FROM users WHERE JSON_CONTAINS_PATH(profile, 'one', '$.address.postal_code', '$.address.prefecture');
+--------+
| name   |
+--------+
| 佐藤   |
| 鈴木   |
| 田中   |
+--------+
postal_codeとprefectureの両方が存在するユーザー(佐藤さんのみ)
mysql> SELECT name FROM users WHERE JSON_CONTAINS_PATH(profile, 'all', '$.address.postal_code', '$.address.prefecture');
+--------+
| name   |
+--------+
| 佐藤   |
+--------+
1 row in set (0.00 sec)

JSON_SEARCH ―どこかにある値をしらべたい

JSON_SEARCHは、JSON全体を対象にして、指定した値がどこかに存在するかを検索する関数です。ヒットした場合はそのパス文字列(例: "$.tags[2]")を返すため、⁠値は知っているけどどこにあるのかわからない」という場面で便利です。

oneallの違い
  • one:最初に見つかった1つのパスだけ返す
  • all:マッチするすべてのパスを配列として返す
※:JSON_SEARCHall「一致するすべてのパスを返す」という意味であり、JSON_CONTAINS_PATHのallとは異なります。
“エンジニア”という文字列をoneで検索
mysql> SELECT name, JSON_SEARCH(profile, 'one', 'エンジニア') AS match_path FROM users;
+--------+--------------------------+
| name   | match_path               |
+--------+--------------------------+
| 佐藤   | "$.attributes.job_title" |
| 鈴木   | NULL                     |
| 田中   | "$.tags[3]"              |
+--------+--------------------------+
3 rows in set (0.00 sec)
“正社員”という文字列をallで検索。田中さんは複数のパスでマッチするため配列で返される
mysql> SELECT name, JSON_SEARCH(profile, 'all', '正社員') AS match_path FROM users;
+--------+-----------------------------------------------+
| name   | match_path                                    |
+--------+-----------------------------------------------+
| 佐藤   | "$.attributes.employment_type"                |
| 鈴木   | "$.attributes.employment_type"                |
| 田中   | ["$.tags[4]", "$.attributes.employment_type"] |
+--------+-----------------------------------------------+
3 rows in set (0.00 sec)

まとめ

MySQLのJSON関数は、柔軟なデータ構造を扱う上で非常に強力なツールです。
今回紹介した関数を活用すれば、タグ情報の配列要素の一致確認、値の抽出、構造の確認など、リレーショナルな表現では難しい操作もSQLだけで実現できます。

ただし、JSON関数を使った検索はパフォーマンス的に重くなりがちです。定常的な検索に使う場合は、Generated Columnとインデックスの併用を検討しましょう。

柔軟な設計を許容するためにJSONを使いつつも、⁠どの値がどこにあるか」を把握できるようにしておくことが、MySQLでのJSON活用を成功させるポイントです。ぜひ、今回のTipsを実案件でも試してみてください。

今回紹介した関数についてより詳細に知りたい場合は、公式ドキュメントの JSON 値を検索する関数 を参考にしてください。

おすすめ記事

記事・ニュース一覧