🎉
MySQL JSON カラム内の配列に含まれる要素を検索する方法
例えば、
CREATE TABLE IF NOT EXISTS `user` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`data` JSON,
PRIMARY KEY (`id`)
) ;
上記のようなスキーマのテーブルに、
INSERT INTO user SET data = '{ "skill": { "tags": ["Python", "JavaScript", "Java"] } }' ;
INSERT INTO user SET data = '{ "skill": { "tags": [ "JavaScript", "Java"] } }' ;
INSERT INTO user SET data = '{ "skill": { "tags": ["Python", "Java"] } }' ;
INSERT INTO user SET data = '{ "skill": { "tags": ["Python", "JavaScript" ] } }' ;
INSERT INTO user SET data = '{ "skill": { "tags": ["Python" ] } }' ;
INSERT INTO user SET data = '{ "skill": { "tags": [ "JavaScript" ] } }' ;
INSERT INTO user SET data = '{ "skill": { "tags": [ "Java"] } }' ;
INSERT INTO user SET data = '{ "skill": { "tags": [ ] } }' ;
という具合に情報を追加し、
SELECT * FROM user ;
+----+-------------------------------------------------------+
| id | data |
+----+-------------------------------------------------------+
| 1 | {"skill": {"tags": ["Python", "JavaScript", "Java"]}} |
| 2 | {"skill": {"tags": ["JavaScript", "Java"]}} |
| 3 | {"skill": {"tags": ["Python", "Java"]}} |
| 4 | {"skill": {"tags": ["Python", "JavaScript"]}} |
| 5 | {"skill": {"tags": ["Python"]}} |
| 6 | {"skill": {"tags": ["JavaScript"]}} |
| 7 | {"skill": {"tags": ["Java"]}} |
| 8 | {"skill": {"tags": []}} |
+----+-------------------------------------------------------+
user
の skill.tags
に Python
を含むレコードを抽出したい場合は、
SELECT * FROM user WHERE JSON_CONTAINS(data, '"Python"', '$.skill.tags') ;
+----+-------------------------------------------------------+
| id | data |
+----+-------------------------------------------------------+
| 1 | {"skill": {"tags": ["Python", "JavaScript", "Java"]}} |
| 3 | {"skill": {"tags": ["Python", "Java"]}} |
| 4 | {"skill": {"tags": ["Python", "JavaScript"]}} |
| 5 | {"skill": {"tags": ["Python"]}} |
+----+-------------------------------------------------------+
上記のようなイメージで JSON_CONTAINS()
を使用することで抽出ができる。
配列の形で指定しても、
SELECT * FROM user WHERE JSON_CONTAINS(data, '["Python"]', '$.skill.tags') ;
+----+-------------------------------------------------------+
| id | data |
+----+-------------------------------------------------------+
| 1 | {"skill": {"tags": ["Python", "JavaScript", "Java"]}} |
| 3 | {"skill": {"tags": ["Python", "Java"]}} |
| 4 | {"skill": {"tags": ["Python", "JavaScript"]}} |
| 5 | {"skill": {"tags": ["Python"]}} |
+----+-------------------------------------------------------+
上記のように同じ結果となり、また、指定した配列の要素を複数にした場合、
SELECT * FROM user WHERE JSON_CONTAINS(data, '["Python", "JavaScript"]', '$.skill.tags') ;
+----+-------------------------------------------------------+
| id | data |
+----+-------------------------------------------------------+
| 1 | {"skill": {"tags": ["Python", "JavaScript", "Java"]}} |
| 4 | {"skill": {"tags": ["Python", "JavaScript"]}} |
+----+-------------------------------------------------------+
SELECT * FROM user WHERE JSON_CONTAINS(data, '["Python", "Java"]', '$.skill.tags') ;
+----+-------------------------------------------------------+
| id | data |
+----+-------------------------------------------------------+
| 1 | {"skill": {"tags": ["Python", "JavaScript", "Java"]}} |
| 3 | {"skill": {"tags": ["Python", "Java"]}} |
+----+-------------------------------------------------------+
AND
検索となる。
もちろん、下記のイメージで OR
検索も可能。
SELECT * FROM user
WHERE JSON_CONTAINS(data, '"Python"', '$.skill.tags')
OR JSON_CONTAINS(data, '"JavaScript"', '$.skill.tags') ;
+----+-------------------------------------------------------+
| id | data |
+----+-------------------------------------------------------+
| 1 | {"skill": {"tags": ["Python", "JavaScript", "Java"]}} |
| 2 | {"skill": {"tags": ["JavaScript", "Java"]}} |
| 3 | {"skill": {"tags": ["Python", "Java"]}} |
| 4 | {"skill": {"tags": ["Python", "JavaScript"]}} |
| 5 | {"skill": {"tags": ["Python"]}} |
| 6 | {"skill": {"tags": ["JavaScript"]}} |
+----+-------------------------------------------------------+
Discussion