MySQL JSON カラム内の配列のに含まれる連想配列の要素を検索する方法

MySQL JSON カラム内の配列に含まれる要素を検索する方法 の続き。

---

例えば、下記のような schema の table があったとして、

CREATE TABLE IF NOT EXISTS `user` (
    `id`    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `data`  JSON,
    PRIMARY KEY (`id`)
) ;

---

そこに下記のように skill という配列に連想配列の要素を INSERT して、

INSERT INTO user (data) VALUES
  ('{"skill": [{"tag": "Python", "has_experience": true},   {"tag": "JavaScript", "has_experience": true},  {"tag": "Java", "has_experience": true}   ]}'),
  ('{"skill": [{"tag": "Python", "has_experience": false},  {"tag": "JavaScript", "has_experience": true},  {"tag": "Java", "has_experience": true}   ]}'),
  ('{"skill": [{"tag": "Python", "has_experience": true},   {"tag": "JavaScript", "has_experience": false}, {"tag": "Java", "has_experience": true}   ]}'),
  ('{"skill": [{"tag": "Python", "has_experience": true},   {"tag": "JavaScript", "has_experience": true},  {"tag": "Java", "has_experience": false}  ]}'),
  ('{"skill": [{"tag": "Python", "has_experience": true},   {"tag": "JavaScript", "has_experience": false}, {"tag": "Java", "has_experience": false}  ]}'),
  ('{"skill": [{"tag": "Python", "has_experience": false},  {"tag": "JavaScript", "has_experience": true},  {"tag": "Java", "has_experience": false}  ]}'),
  ('{"skill": [{"tag": "Python", "has_experience": false},  {"tag": "JavaScript", "has_experience": false}, {"tag": "Java", "has_experience": true}   ]}'),
  ('{"skill": [{"tag": "Python", "has_experience": false},  {"tag": "JavaScript", "has_experience": false}, {"tag": "Java", "has_experience": false}  ]}');

---

中身が下記のようになっている場合、

SELECT * FROM user ;

+----+---------------------------------------------------------------------------------------------------------------------------------------------------+
| id | data                                                                                                                                              |
+----+---------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": true}, {"tag": "Java", "has_experience": true}]}    |
|  2 | {"skill": [{"tag": "Python", "has_experience": false}, {"tag": "JavaScript", "has_experience": true}, {"tag": "Java", "has_experience": true}]}   |
|  3 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": false}, {"tag": "Java", "has_experience": true}]}   |
|  4 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": true}, {"tag": "Java", "has_experience": false}]}   |
|  5 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": false}, {"tag": "Java", "has_experience": false}]}  |
|  6 | {"skill": [{"tag": "Python", "has_experience": false}, {"tag": "JavaScript", "has_experience": true}, {"tag": "Java", "has_experience": false}]}  |
|  7 | {"skill": [{"tag": "Python", "has_experience": false}, {"tag": "JavaScript", "has_experience": false}, {"tag": "Java", "has_experience": true}]}  |
|  8 | {"skill": [{"tag": "Python", "has_experience": false}, {"tag": "JavaScript", "has_experience": false}, {"tag": "Java", "has_experience": false}]} |
+----+---------------------------------------------------------------------------------------------------------------------------------------------------+

---

user の skill に tag="Python", has_experience=true　があるレコードを抽出したい場合、下記のように JSON_CONTAINS() を使用して抽出できる。

SELECT * FROM user WHERE JSON_CONTAINS(data, '{"tag": "Python", "has_experience": true}', '$.skill') ;

+----+--------------------------------------------------------------------------------------------------------------------------------------------------+
| id | data                                                                                                                                             |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": true}, {"tag": "Java", "has_experience": true}]}   |
|  3 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": false}, {"tag": "Java", "has_experience": true}]}  |
|  4 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": true}, {"tag": "Java", "has_experience": false}]}  |
|  5 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": false}, {"tag": "Java", "has_experience": false}]} |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------+

---

配列の形で指定しても同じ結果となる。

SELECT * FROM user WHERE JSON_CONTAINS(data, '[{"tag": "Python", "has_experience": true}]', '$.skill') ;

+----+--------------------------------------------------------------------------------------------------------------------------------------------------+
| id | data                                                                                                                                             |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": true}, {"tag": "Java", "has_experience": true}]}   |
|  3 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": false}, {"tag": "Java", "has_experience": true}]}  |
|  4 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": true}, {"tag": "Java", "has_experience": false}]}  |
|  5 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": false}, {"tag": "Java", "has_experience": false}]} |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------+

---

また、指定した配列の要素を複数にした場合、

SELECT * FROM user WHERE JSON_CONTAINS(data, '[{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": true}]', '$.skill') ;

+----+-------------------------------------------------------------------------------------------------------------------------------------------------+
| id | data                                                                                                                                            |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": true}, {"tag": "Java", "has_experience": true}]}  |
|  4 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": true}, {"tag": "Java", "has_experience": false}]} |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------+

---

SELECT * FROM user WHERE JSON_CONTAINS(data, '[{"tag": "Python", "has_experience": true}, {"tag": "Java", "has_experience": true}]', '$.skill') ;

+----+-------------------------------------------------------------------------------------------------------------------------------------------------+
| id | data                                                                                                                                            |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": true}, {"tag": "Java", "has_experience": true}]}  |
|  3 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": false}, {"tag": "Java", "has_experience": true}]} |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------+

AND 検索となる。

---

もちろん、下記のように OR 検索もできる。

SELECT * FROM user
  WHERE JSON_CONTAINS(data, '{"tag": "Python",      "has_experience": true}', '$.skill')
    OR  JSON_CONTAINS(data, '{"tag": "JavaScript",  "has_experience": true}', '$.skill') ;

+----+--------------------------------------------------------------------------------------------------------------------------------------------------+
| id | data                                                                                                                                             |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": true}, {"tag": "Java", "has_experience": true}]}   |
|  2 | {"skill": [{"tag": "Python", "has_experience": false}, {"tag": "JavaScript", "has_experience": true}, {"tag": "Java", "has_experience": true}]}  |
|  3 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": false}, {"tag": "Java", "has_experience": true}]}  |
|  4 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": true}, {"tag": "Java", "has_experience": false}]}  |
|  5 | {"skill": [{"tag": "Python", "has_experience": true}, {"tag": "JavaScript", "has_experience": false}, {"tag": "Java", "has_experience": false}]} |
|  6 | {"skill": [{"tag": "Python", "has_experience": false}, {"tag": "JavaScript", "has_experience": true}, {"tag": "Java", "has_experience": false}]} |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------+