MySQLのJSON型は、柔軟なデータ構造をそのまま格納できる点で非常に便利です。ユーザー設定やタグ情報、外部APIのレスポンスなど、テーブル構造に縛られずに情報を保存したい場面では重宝します。
ただし、複雑なJSONデータをそのまま詰め込んだ場合には注意が必要です。たとえばSHOW CREATE TABLEを見てもJSONカラムの中身までは確認できないため、
そんなときに頼りになるのが、MySQLが提供する各種JSON関数です。構造の把握や値の抽出、特定の値の有無の確認など、うまく使えばJSONの中身とつき合いやすくなります。
本稿では、いくつかの代表的なJSON関数をサンプルデータとともに紹介しながら、その使いどころや注意点を実践的に紹介していきます。なお、今回の検証に使用しているMySQLのバージョンは8.
サンプルデータの準備
検証しやすいように、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データの検索をする関数
それでは、先ほど用意したサンプルデータを使って、MySQLでJSONデータを扱う関数の動作を順番に確認していきましょう。
JSON_CONTAINS ―完全一致で検索
JSON_は、指定した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_は、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に
JSON_
- JSON_
OVERLAPSはOR条件 （1つでも一致すればOK） - JSON_
CONTAINSはAND条件 （すべて含んでいなければNG）
たとえば、JSON_を第2引数に渡した場合、JSON_
JSON_VALUE ―スカラー値の抽出
JSON_は、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_がありますが、CAST( JSON_と同等の関数になります。
JSON_KEYS ―属性の一覧を取得したいとき
JSON_は、指定した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_は、指定したJSONパスが存在するかどうかをチェックする関数です。
第2引数にはoneall
JSON_CONTAINS_PATH(json_doc, 'one' or 'all', path1, path2, ...)
oneとallの違い-
one：パスのうちいずれか1つでも存在すればTRUEを返すall：すべてのパスが存在していないとFALSEを返す
構造が柔軟なJSONカラムを扱う場面で、
以下は、profileカラムの中のaddress.
mysql> SELECT name FROM users WHERE JSON_CONTAINS_PATH(profile, 'one', '$.address.postal_code'); +--------+ | name | +--------+ | 佐藤 | | 鈴木 | +--------+ 2 rows in set (0.00 sec)
mysql> SELECT name FROM users WHERE JSON_CONTAINS_PATH(profile, 'one', '$.address.postal_code', '$.address.prefecture'); +--------+ | name | +--------+ | 佐藤 | | 鈴木 | | 田中 | +--------+
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_
oneとallの違い-
one：最初に見つかった1つのパスだけ返すall：マッチするすべてのパスを配列として返す
- ※：
JSON_のSEARCH allは「一致するすべてのパスを返す」 という意味であり、JSON_ CONTAINS_ PATHの allとは異なります。
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)
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を使いつつも、
今回紹介した関数についてより詳細に知りたい場合は、公式ドキュメントの JSON 値を検索する関数 を参考にしてください。