嚴華 发表于 2025-3-22 08:04:48

mysql中查询json的本领

前置工作

CREATE TABLE `mk_task_record` (
`task_id` int NOT NULL AUTO_INCREMENT,
`task_name` varchar(50) DEFAULT NULL,
`result_json` json DEFAULT NULL,
`result_str` longtext,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`task_id`),
KEY `task_name` (`task_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
插入数据:
插入带转义符的json:
INSERT INTO `test`.`mk_task_record`(`task_id`, `task_name`, `result_json`, `result_str`, `create_time`, `update_time`) VALUES (1, '哈哈1', '{\"age\": 22, \"name\": \"李四\", \"major\": \"计算机科学\", \"university\": \"清华大学\", \"is_graduated\": false}', '{\r\n\"name\": \"李四\",\r\n\"age\": 22,\r\n\"major\": \"计算机科学\",\r\n\"university\": \"清华大学\",\r\n\"is_graduated\": false\r\n}', '2025-01-01 00:13:49', '2025-01-01 00:13:52');

INSERT INTO `test`.`mk_task_record`(`task_id`, `task_name`, `result_json`, `result_str`, `create_time`, `update_time`) VALUES (2, '哈哈2', '{\"age\": 22, \"name\": \"张三\", \"major\": \"计算机科学\", \"university\": \"清华大学\", \"is_graduated\": false}', '{\r\n\"name\": \"张三\",\r\n\"age\": 22,\r\n\"major\": \"计算机科学\",\r\n\"university\": \"清华大学\",\r\n\"is_graduated\": false\r\n}', '2025-01-01 00:13:49', '2025-01-01 00:13:52');

插入不带转义符的json:
INSERT INTO `test`.`mk_task_record`( `task_name`, `result_json`, `result_str`, `create_time`, `update_time`) VALUES ( '哈哈3', '{"name": "王五", "age": 35, "address": {"street": "123 Main St", "city": "Chicago"}}', '{"name": "王五", "age": 35, "address": {"street": "123 Main St", "city": "Chicago"}}', '2025-01-01 00:13:49', '2025-01-01 00:13:52');
INSERT INTO `test`.`mk_task_record`( `task_name`, `result_json`, `result_str`, `create_time`, `update_time`) VALUES ( '哈哈4', '{"name": "赵六", "age": 30, "address": {"street": "123 Main St", "city": "Chicago"}}','{"name": "赵六", "age": 30, "address": {"street": "123 Main St", "city": "Chicago"}}', '2025-01-01 00:13:49', '2025-01-01 00:13:52');



[*]result_json是json格式;
[*]result_result是longtext格式;
颠末现实测试,json、varchar、text范例的json格式都可以使用以下的函数,且结果相同。
一、JSON_EXTRACT 提取指定命据

1.1 提取简单值

SELECT JSON_EXTRACT(result_str,'$.name') from mk_task_record;
SELECT JSON_EXTRACT(result_json,'$.name') from mk_task_record;


[*]json范例:
https://i-blog.csdnimg.cn/direct/26d830ad6d41417e96d1cd916c897e34.png
[*]text范例
https://i-blog.csdnimg.cn/direct/35d96893e0ff454e8781ed229536133c.png
结果也带引号;
怎样去掉引号呢?
——检察【四、JSON_UNQUOTE 取消双引号】


[*]作条件查询
select *from mk_task_record where json_extract(result_str,'$.name') ='张三';
select *from mk_task_record where json_extract(result_json,'$.name') ='张三';
两条sql返回的都一样:
https://i-blog.csdnimg.cn/direct/01a2c4241f1d4a9e891c07b6b3601289.png
https://blog.csdn.net/stxyg/article/details/143718543
二、字段->'$.json属性’进行查询条件,以及数据反显

select result_json->'$.name' from mk_task_record;
https://i-blog.csdnimg.cn/direct/b33f924fc8b046c1861a333062d1ca62.png
作为查询条件,不用转移双引号。
三、JSON_UNQUOTE 取消双引号

JSON_UNQUOTE() 函数取消双引号引用 JSON 值,并将结果作为字符串返回。
3.1 语法:

JSON_UNQUOTE(json_val)
参数说明:
json_val:必须的。一个字符串。
返回值:
取消双引号引用 JSON 值
返回NULL情况:参数为 NULL。
报错情况:不是有效的 JSON 字符串文本。可以大概识别下表中的转义字符:
":双引号 "
\b:退格字符
\f:换页符
\n:换行符
\r:回车符
\t:制表符
\:反斜杠
\uXXXX:Unicode 值 XXXX 的 UTF-8 字节
3.2 示例:

SELECT JSON_UNQUOTE(JSON_EXTRACT(result_json,'$.name')) from mk_task_record;
https://i-blog.csdnimg.cn/direct/bc145e1e331847f9b6ec3407fffbdb5d.png
五、JSON_SEARCH 查找

JSON_SEARCH() 函数返回一个给定字符串在一个 JSON 文档中的路径。
5.1 语法:

JSON_SEARCH(json, one_or_all, search_str)
JSON_SEARCH(json, one_or_all, search_str, escape_char)
JSON_SEARCH(json, one_or_all, search_str, escape_char, path)

参数说明:


[*]on:必须的。一个 JSON 文档。
[*]one_or_all:必须的。可用值:‘one’, ‘all’。
[*]规则如下:‘one’:返回第一个匹配的路径。‘all’:返回所有匹配的路径。所有的路径会包装在一个数组内返回。
[*]search_str:必须的。被搜刮的字符串。 您可以在 search_str 参数中使用 % 和 _ 通配符,就像 LIKE 一样:% 匹配恣意数目的恣意字符。_ 匹配一个恣意字符。
[*]escape_char:可选的。 假如 search_str 中包含 % 和 _,需要在他们之前添加转移字符。默认是 \。
[*]path:可选的。只能在此路径下进行搜刮。
返回值:一个路径字符串或者由多个路径组成的数组。返回 NULL情况:未搜刮到指定的字符串。JSON 文档中不存在指定的 path。恣意一个参数为 NULL。
报错情况:json 不是有效的 JSON 文档。path 不是有效的路径表达式。
5.2 示例:

select *from mk_task_record whereJSON_SEARCH(result_json,'one','张三');
六、JSON_SET 插入或更新数据

JSON_SET() 函数在一个 JSON 文档中插入或更新数据并返回新的 JSON 文档。它相当于是 JSON_INSERT() 和 JSON_REPLACE() 的组合。
6.1 语法:

JSON_SET(json, path, value[, path2, value2] ...)
参数说明:


[*]json:必须的。被修改的 JSON 文档。
[*]path:必须的。一个有效的路径表达式,它不能包含 * 或 **。
[*]value:必须的。要设置的数据。
插入或更新数据并返回新的 JSON 文档。规则如下:


[*]存在路径:更新。
[*]不存在路径:添加。
[*]若value 为字符串:直接插入。
[*]返回 NULL情况:JSON 文档或者路径为 NULL。
报错情况:json 不是有效的 JSON 文档。
path 不是有效的路径表达式或者此中包含 * 或 **。
6.2 示例:

UPDATE mk_task_record SET result_str = JSON_SET(result_str, '$.name', '王麻子', '$.age', '36')where task_id =1;
https://i-blog.csdnimg.cn/direct/762e7ce5a6f44c2bb08c694386ad7091.png
七、JSON_CONTAINS 判定是否包含

JSON_CONTAINS() 函数检查一个 JSON 文档中是否包含另一个 JSON 文档。
7.1 语法:

JSON_CONTAINS(target_json, candidate_json)
JSON_CONTAINS(target_json, candidate_json, path)
参数说明:


[*]target_json必须的。一个 JSON 文档。
[*]candidate_json:必须的。被包含的 JSON 文档。
[*]path:可选的。一个路径表达式。
[*]返回值:
包含:返回1。
不包含:返回0。
返回 NULL情况:JSON 文档中不存在指定的路径。
恣意一个参数为 NULL。
报错情况:
json 不是有效的 JSON 文档。path 不是有效的路径表达式。
示例:
SELECT * FROM mk_task_record WHERE JSON_CONTAINS(result_json, '"张三"','$.name');
https://i-blog.csdnimg.cn/direct/4b813f250f0449eb93c850ba1b86e062.png
留意:"张三"有双引号肯定加,否则报错:
https://i-blog.csdnimg.cn/direct/5e29a4c1cca948c98278f83f8942fe84.png
八、JSON_REMOVE 删除指定命据

JSON_REMOVE() 函数从一个 JSON 文档中删除由路径指定的数据并返回修改后的 JSON 文档。
8.1 语法:

JSON_REMOVE(json, path[, path] ...)

参数说明:


[*]json:必须的。一个 JSON 文档。
[*]path:必须的。一个有效的路径表达式,它不能包含 * 或 **。
返回值:删除后的JSON文档。
8.2 示例:

select JSON_REMOVE(result_str,'$.major') from mk_task_record where task_id =1;
九、JSON_REPLACE 替换数据

JSON_REPLACE() 函数在一个 JSON 文档中替换已存在的数据并返回新的 JSON 文档
9.1 语法:

JSON_REPLACE(json, path, value[, path2, value2] ...)

参数说明:


[*]json:必须的。被修改的 JSON 文档。
[*]path:必须的。一个有效的路径表达式,它不能包含 * 或 **。
[*]value:必须的。新的数据。
[*]返回值:替换后的JSON文档。回 NULL情况:JSON 文档或者路径为 NULL。
报错情况:
json 不是有效的 JSON 文档。path 不是有效的路径表达式或者此中包含 * 或 **。
9.2 示例:

select JSON_REPLACE(result_str,'$.university','复旦大学') from mk_task_record where task_id =1;
https://i-blog.csdnimg.cn/direct/990c505440d547d098efc950933ded2f.png
拓展:
https://www.qianduange.cn/article/15824.html

免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。
页: [1]
查看完整版本: mysql中查询json的本领