概要
MySQL 5.7.8 以降で使える JSON 型を試したみました JSON データの挿入方法から特殊な JSON クエリを使った検索方法まで紹介したいと思います
環境
- macOS 11.4
- MySQL 8.0.25
準備
まずは JSON 型のカラムを持つテーブルの定義しましょう
- CREATE DATABASE test;
- USE test;
- CREATE TABLE user (id int(11) NOT NULL AUTO_INCREMENT, profile json DEFAULT NULL, PRIMARY KEY (id));
型名にはその名の通り「json」という型があるのでこれを使います ちゃんと json 型で登録されています
- SHOW CREATE TABLE user \G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`profile` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)
データ登録
次に JSON 型のデータを登録してみます
データを登録する際はシングルクオートで全体を囲って中のキーや値はダブルクオートで囲います
- INSERT INTO user VALUES (null, ‘{“name”:“hawksnowlog”}’);
- INSERT INTO user VALUES (null, ‘{“name”:“hawk”}’);
- INSERT INTO user VALUES (null, ‘{“name”:“snowlog”}’);
JSON_EXTRACT
ここからは特殊な JSON 型のクエリを使っていきます
JSON_EXTRACT クエリは特定の Json ドキュメントを取得するためのクエリです
例えば name キーの値だけを取得する場合は以下のようにします
- SELECT JSON_EXTRACT(profile, ‘$.name’) FROM user;
+---------------------------------+
| JSON_EXTRACT(profile, '$.name') |
+---------------------------------+
| "hawksnowlog" |
| "hawk" |
| "snowlog" |
+---------------------------------+
3 rows in set (0.00 sec)
これと WHERE 句を組み合わせて特定の JSON ドキュメントを含んだレコードのみを取得することもできます
- SELECT * FROM user WHERE JSON_EXTRACT(profile, ‘$.name’) = ‘hawksnowlog’;
+----+-------------------------+
| id | profile |
+----+-------------------------+
| 1 | {"name": "hawksnowlog"} |
+----+-------------------------+
1 row in set (0.00 sec)
LIKE 句と組み合わせるとより柔軟な検索が可能になります
- SELECT * FROM user WHERE JSON_EXTRACT(profile, ‘$.name’) LIKE ‘%hawk%’;
+----+-------------------------+
| id | profile |
+----+-------------------------+
| 1 | {"name": "hawksnowlog"} |
| 2 | {"name": "hawk"} |
+----+-------------------------+
2 rows in set (0.00 sec)
JSON_SEARCH
JSON_SEARCH クエリは特定のドキュメントが存在するパスを取得することができるクエリです
2 つ目の引数には「one」or「all」のどちらかを指定します 前者は一致した最初の JSON ドキュメントのパスだけを取得します 後者は一致したすべての JSON ドキュメントのパスを取得します 今回は一致する JSON ドキュメントが 1 つしかないので one でも all でもパスは 1 つになります
- SELECT JSON_SEARCH(profile, ‘one’, ‘hawksnowlog’) FROM user;
+--------------------------------------------+
| JSON_SEARCH(profile, 'one', 'hawksnowlog') |
+--------------------------------------------+
| "$.name" |
| NULL |
| NULL |
+--------------------------------------------+
3 rows in set (0.00 sec)
応用して先程の JSON_EXTRACT と組み合わせることもできます
- SELECT JSON_SEARCH(profile, ‘all’, ‘%hawk%’) FROM user WHERE JSON_EXTRACT(profile, ‘$.name’) LIKE ‘%hawk%’;
+---------------------------------------+
| JSON_SEARCH(profile, 'all', '%hawk%') |
+---------------------------------------+
| "$.name" |
| "$.name" |
+---------------------------------------+
2 rows in set (0.00 sec)
JSON_CONTAINS
JSON_CONTAINS は各レコードに特性のドキュメントが含まれているか確認することができるクエリです 含まれている場合は「1」含まれていない場合は「0」が返ってきます
2 つ目の引数に検索する値を指定するのですが文字列を指定する場合はシングルクオートで囲った上で中をダブルクオートでも囲う必要があるので注意しましょう
以下は name フィールドに hawksnowlog という値を含む JSON ドキュメントが各レコードで含まれているか確認するクエリです
- SELECT JSON_CONTAINS(profile, ‘“hawksnowlog”’, ‘$.name’) FROM user;
+---------------------------------------------------+
| JSON_CONTAINS(profile, '"hawksnowlog"', '$.name') |
+---------------------------------------------------+
| 1 |
| 0 |
| 0 |
+---------------------------------------------------+
3 rows in set (0.00 sec)
JSON_CONTAINS も JSON_EXTRACT と組み合わせて検索することができます
-
SELECT JSON_CONTAINS(profile, '"hawksnowlog"', '$.name') FROM user WHERE JSON_EXTRACT(profile, '$.name') LIKE '%hawk%';
+---------------------------------------------------+
| JSON_CONTAINS(profile, '"hawksnowlog"', '$.name') |
+---------------------------------------------------+
| 1 |
| 0 |
+---------------------------------------------------+
2 rows in set (0.00 sec)
JSON_VALUE
指定のキー (パス) の値だけを取り出すことができるクエリです
- SELECT JSON_VALUE(profile, ‘$.name’) from user;
+-------------------------------+
| JSON_VALUE(profile, '$.name') |
+-------------------------------+
| hawksnowlog |
| hawk |
| snowlog |
+-------------------------------+
3 rows in set (0.01 sec)
JSON_EXTRACT に似ていますがこちらは返り値の型変換が行えたり空 or エラー時のハンドリングができます
- SELECT JSON_VALUE(profile, ‘$.hoge’ RETURNING CHAR DEFAULT ‘default_value!’ ON EMPTY) from user;
+--------------------------------------------------------------------------------+
| JSON_VALUE(profile, '$.hoge' RETURNING CHAR DEFAULT 'default_value!' ON EMPTY) |
+--------------------------------------------------------------------------------+
| default_value! |
| default_value! |
| default_value! |
+--------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
ちなみに JSON_VALUE も WHERE 句に使えます
- SELECT * FROM user WHERE JSON_VALUE(profile, ‘$.name’) = ‘hawk’;
その他クエリ
- JSON_OVERLAPS･･･ 2 つの JSON ドキュメントを比較することができるクエリ
- MEMBER OF･･･配列内に特定の値が含まれているかチェックするクエリ
Ruby から使ってみる
mysql2 を使って JSON 系のクエリを発行してみました サンプルコードを紹介します
require 'mysql2'
require 'json'
client = Mysql2::Client.new(:host => "localhost", :username => "root", :database => 'test')
query = "select * from user"
records = client.query(query)
records.each do |record|
puts record["id"]
profile = JSON.parse(record["profile"])
puts profile["name"]
end
query = "SELECT JSON_VALUE(profile, '$.name') from user;"
records = client.query(query)
records.each do |record|
puts record
end
結果は以下の通りです JSON 系のクエリを発行した場合は AS 句を使って結果のカラムを変更してあげると扱いやすいと思います
1
hawksnowlog
2
hawk
3
snowlog
hawksnowlog
hawk
snowlog
最後に
気になるのは性能面かなと思います 全レコードを検索するような場合に更に JSON オブジェクトを展開するとなると検索のオーダが跳ね上がるのでそこは MySQL で頑張るのかコード側で頑張るのか (もしくは別のコンポーネントを使うのか) などを考えたほうが良いかなと思います
0 件のコメント:
コメントを投稿