MySQL 5.7 JSONの検索


この記事は[Wanoアドベントカレンダー](https://qiita.com/advent-calendar/2018/wano-group)の12日目の記事です。

最近DBのカラムはjsonのformat保存が多くて、たまに検索したいの時、色々分からないので、
ちょっとまとめします。

一番よく混乱するのはやはり配列の検索ですね。

例：

```
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | json                                                                                                                                                                 |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | {"id": 1, "items": [{"id": 100, "name": "itemA"}, {"id": 200, "name": "itemB"}]}                                                                                     |
|  2 | [{"id": 2, "items": [{"id": 100, "name": "itemA"}, {"id": 200, "name": "itemB"}]}]                                                                                   |
|  3 | [{"id": 3, "items": [{"id": 100, "name": "itemA"}, {"id": 200, "name": "itemB"}]}, {"id": 4, "items": [{"id": 100, "name": "itemA"}, {"id": 200, "name": "itemB"}]}] |
|  4 | [{"id": 5, "items": [{"id": 200, "name": "itemB"}]}, {"id": 6, "items": [{"id": 100, "name": "itemA"}, {"id": 200, "name": "itemB"}]}]                               |
|  5 | [{"id": 7, "items": [{"id": 200, "name": "itemB"}]}, {"id": 8, "items": [{"id": 200, "name": "itemB"}]}]                                                             |
+----+---------------------+---------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```

"name"は"itemA"持ってるのjson 検索したいの時：


検索1:

```

SELECT * FROM json_table WHERE JSON_CONTAINS(json, '{"name": "itemA"}', '$.items');

result:

+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | json                                                                                                                                                                 |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | {"id": 1, "items": [{"id": 100, "name": "itemA"}, {"id": 200, "name": "itemB"}]}                                                                                     |
+----+---------------------+---------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```

検索2:

```
SELECT * FROM test WHERE JSON_CONTAINS(json, '{"name": "itemA"}', '$[0].items'); 

result:

+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | json                                                                                                                                                                 |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | {"id": 1, "items": [{"id": 100, "name": "itemA"}, {"id": 200, "name": "itemB"}]}                                                                                     |
|  2 | [{"id": 2, "items": [{"id": 100, "name": "itemA"}, {"id": 200, "name": "itemB"}]}]                                                                                   |
|  3 | [{"id": 3, "items": [{"id": 100, "name": "itemA"}, {"id": 200, "name": "itemB"}]}, {"id": 4, "items": [{"id": 100, "name": "itemA"}, {"id": 200, "name": "itemB"}]}] |
+----+---------------------+---------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```

検索3:

```
SELECT * FROM test WHERE JSON_CONTAINS(json, '{"name": "itemA"}', '$[1].items'); 

result:

+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | json                                                                                                                                                                 |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  3 | [{"id": 3, "items": [{"id": 100, "name": "itemA"}, {"id": 200, "name": "itemB"}]}, {"id": 4, "items": [{"id": 100, "name": "itemA"}, {"id": 200, "name": "itemB"}]}] |
|  4 | [{"id": 5, "items": [{"id": 200, "name": "itemB"}]}, {"id": 6, "items": [{"id": 100, "name": "itemA"}, {"id": 200, "name": "itemB"}]}]                               |
+----+---------------------+---------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```

検索4:


```
SELECT * FROM test WHERE JSON_SEARCH(json,'all','itemA') LIKE "%name%";


result:

+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | json                                                                                                                                                                 |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | {"id": 1, "items": [{"id": 100, "name": "itemA"}, {"id": 200, "name": "itemB"}]}                                                                                     |
|  2 | [{"id": 2, "items": [{"id": 100, "name": "itemA"}, {"id": 200, "name": "itemB"}]}]                                                                                   |
|  3 | [{"id": 3, "items": [{"id": 100, "name": "itemA"}, {"id": 200, "name": "itemB"}]}, {"id": 4, "items": [{"id": 100, "name": "itemA"}, {"id": 200, "name": "itemB"}]}] |
|  4 | [{"id": 5, "items": [{"id": 200, "name": "itemB"}]}, {"id": 6, "items": [{"id": 100, "name": "itemA"}, {"id": 200, "name": "itemB"}]}]                               |
+----+---------------------+---------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```

感想：
使うのタイミングに関しては
明確な配列の検索の時、JSON_CONTAINSは便利と思います。
複雑な配列検索したいの時、JSON_SEARCHのほうがいいかも。