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

[MySQL JSON カラム内の配列に含まれる要素を検索する方法](https://qiita.com/ukyooo/items/19da224866affa97a211) の続き。

少し出力を直したので更新してしまったが、最初に投稿したのはちょうど 1 年前くらい。

* see: [編集履歴](https://qiita.com/ukyooo/items/19da224866affa97a211/revisions)

----

例えば、下記のような 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}]} |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------+
```