MySQL 8.0でJSONデータ検索がさらに便利に！新機能紹介
JSONデータの検索方法
MySQLでJSONデータを検索するには、主に以下の3つの関数を使用します。
1 JSON_EXTRACT関数
特定のパスに一致するJSONドキュメントの一部を抽出します。
SELECT JSON_EXTRACT(json_column, '$.path.to.value') FROM your_table;
2 JSON_CONTAINS関数
JSONドキュメント内に特定のJSONドキュメントが含まれているかどうかを調べます。
SELECT JSON_CONTAINS(json_column, '$.path.to.value', '$') FROM your_table;
SELECT JSON_CONTAINS_PATH(json_column, 'all', '$.items[*].price > 100') FROM your_table;
その他の便利な関数
上記以外にも、JSONデータの検索に役立つ関数がいくつか用意されています。
- JSON_UNQUOTE関数: JSON文字列からクォートを取り除きます。
- JSON_PRETTY関数: JSONデータを整形して表示します。
- JSON_LENGTH関数: JSONドキュメントの要素数を返します。
- JSON_TYPE関数: JSON値の型を返します。
検索例の紹介
以下に、具体的な検索例をいくつか紹介します。
例1：特定のキーの値を検索
SELECT JSON_EXTRACT(data, '$.name') FROM users WHERE id = 1;
このクエリは、usersテーブルのidが1のレコードのdata列にあるJSONデータからnameキーの値を抽出します。
例2：特定のパスに一致するドキュメントをすべて検索
SELECT * FROM products WHERE JSON_CONTAINS(details, '$.category', 'electronics');
このクエリは、productsテーブルのdetails列にあるJSONデータからcategoryキーの値がelectronicsであるドキュメントを持つすべてのレコードを検索します。
例3：JSON配列の要素をすべて検索
SELECT JSON_EXTRACT(data, '$.items[*]') FROM orders WHERE id = 123;
まとめ
MySQLでJSONデータを検索するには、JSON_EXTRACT関数、JSON_CONTAINS関数、JSON_CONTAINS_PATH関数などを組み合わせて使用します。これらの関数を使いこなすことで、複雑なJSONデータ構造から必要な情報効率的に検索することができます。
MySQLでJSONデータの検索を行うサンプルコード
テーブルの作成
まず、JSONデータを格納するためのテーブルを作成します。
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
data JSON NOT NULL
);
このコードは、usersという名前のテーブルを作成し、id、name、dataという3つのカラムを定義します。idカラムは主キーとして設定され、自動的にインクリメントされます。nameカラムは文字列型で、最大255文字まで保存できます。dataカラムはJSON型で、JSONデータを保存します。
データの挿入
次に、usersテーブルにデータ挿入します。
INSERT INTO users (name, data) VALUES ('Taro Yamada', '{"age": 30, "address": "Tokyo, Japan"}');
INSERT INTO users (name, data) VALUES ('Hanako Sato', '{"age": 25, "address": "Osaka, Japan"}');
このコードは、usersテーブルに2つのレコードを挿入します。最初のレコードは、名前が「Taro Yamada」、年齢が30歳、住所が「Tokyo, Japan」のユーザーを表します。2番目のレコードは、名前が「Hanako Sato」、年齢が25歳、住所が「Osaka, Japan」のユーザーを表します。
JSONデータの検索
最後に、JSONデータの検索を行います。
SELECT JSON_EXTRACT(data, '$.name') FROM users WHERE id = 1;
Taro Yamada
SELECT * FROM users WHERE JSON_CONTAINS(data, '$.age', 30);
このクエリは、usersテーブルのdata列にあるJSONデータからageキーの値が30であるドキュメントを持つすべてのレコードを検索します。結果は以下のようになります。
id | name | data
---+---------------+---------------------------------
1 | Taro Yamada | {"age": 30, "address": "Tokyo, Japan"}
SELECT JSON_EXTRACT(data, '$.items[*]') FROM orders WHERE id = 123;
まとめ
上記以外にも、MySQL 8.0ではJSONデータを操作するための様々な関数が用意されています。詳細については、MySQL 8.0 リファレンスマニュアルを参照してください。
MySQLでJSONデータを検索するその他の方法
->演算子を使用して、JSONドキュメント内の要素にアクセスすることもできます。
SELECT data -> '$.name' FROM users WHERE id = 1;
JSON_QUERY関数は、JSONPathを使用してJSONドキュメントを検索できます。
SELECT JSON_QUERY(data, '$.items[?(@.price > 100)]') FROM orders WHERE id = 123;
MySQL全文検索機能を使用して、JSONデータ内のテキストを検索することもできます。
SELECT * FROM products WHERE MATCH(details) AGAINST ('electronics');
まとめ
これらの方法は、それぞれ異なる用途に適しています。状況に応じて適切な方法を選択してください。
- JSON_EXTRACT関数: 特定のキーの値を検索する場合に適しています。
- ->演算子: シンプルなJSONデータの検索に適しています。
- JSON_QUERY関数: 複雑なJSONPathクエリを実行する場合に適しています。
mysql json mysql-json
