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"]}}                   |
+----+-------------------------------------------------------+