この記事はWanoアドベントカレンダーの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のほうがいいかも。