mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan" |
+---------------------------------------------------------+
单个json查询
表字段：
| id |
info |
created_at |
| 1 |
["Alx","Bob"] |
2020-09-22 19:00:00 |
select * from 表名 where json_contains(查询的字段, json_array('查询的数据'));
举例：
select * from table where json_contains(info, json_array('Alx'));
表字段：
| id |
info |
created_at |
| 1 |
{"name": "Alx", "age": 12} |
2020-09-22 19:00:00 |
| 2 |
{"name": "Bob", "age": 12} |
2020-09-22 19:00:00 |
模糊搜索
SELECT * FROM 表名 WHERE json_extract(字段名,"$.json中key") like '%搜索值%';
举例：
SELECT * FROM table WHERE json_extract(info,"$.name") like '%Bob%';
SELECT * FROM 表名 WHERE json_extract(字段名,"$.json中key") = '搜索值';
举例：
SELECT * FROM table WHERE json_extract(info,"$.name") = 'Bob';
数组json查询
表字段：
| id |
info |
created_at |
| 1 |
[{"name": "Alx", "age": 12},{"name": "Bob", "age": 12}] |
2020-09-22 19:00:00 |
| 2 |
[{"name": "Bob", "age": 12},{name": "Tim", "age": 12}] |
2020-09-22 19:00:00 |
模糊搜索
SELECT * FROM 表名 WHERE json_extract(字段名,"$[0].json中key") like '%搜索值%';
举例：
SELECT * FROM table WHERE json_extract(info,"$[0].name") like "%Bob%";
SELECT * FROM 表名 WHERE json_extract(字段名,"$[0].json中key") = '搜索值';
举例：
SELECT * FROM table WHERE json_extract(info,"$[0].name") = "%Bob%";
注：MySQL版本5.7及以上
网友评论