MySQL - json_search 小结

在开发过程中遇到某个表中采用text格式存储了压缩后的JSON字符串，在代码中通过 json_search方法对 JSON 中的内容进行搜索，特此整理有关json_search的内容。MySQL在5.7的官方文档中就有关于JSON的相关说明，更详细的可直接参考官方文档。

JSON_SEARCH

用法

JSON_SEARCH(json_doc，one_or_all，search_str [，escape_char [，path] ...])

说明

通过JSON_SEARCH函数，返回符合查询条件的key对应的的JSON路径所组成的数组，若不存在，则返回NULL

以下情况返回NULL

若 json_doc ，search_str，或path参数中任何一个为NULL，则返回NULL
文档中不存在path
search_str未找到

以下情况报错

json_doc为不合法的JSON文档
path为不合法的路径表达式
one_or_all 参数非 one 或 all

字段解释

json_doc：要查询的Json 文档
one_or_all：查询的终止条件
- one：搜索在第一个匹配项后终止，并返回一个路径字符串。未定义首先考虑哪个匹配。
- all：搜索返回所有匹配的路径字符串，因此不包括重复的路径。如果有多个字符串，它们将自动包装为一个数组。数组元素的顺序是不确定的。
search_str：搜索参数
- escape_char：指定转义符。escape_char 参数指定时要求必须是常量（为空或者一个字符），当escape_char参数为NULL或者不存在的情况下，系统默认使用 \ 作为转义字符。
- path指定的范围始终是要搜索或对其进行操作的文档，并以前导$字符表示。路径分支由句点字符 .分隔。数组中的单元格由[N]表示，和正常访问数组元素格式相同，例如 $.x 代表JSON文档中x对应的值，$[1].y代表JSON文档中第二位数据元素中y所对应的值
注：在search_str中，通配符 %和 _ 可以如同在 LIKE 上一样运行，其中 % 用于匹配多个字符（包括0）， _ 则仅可匹配一个字符

官方示例

mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

对比one和all的区别

很直观的能看除one获取到一个符合条件的路径后就终止了，而all则返回全部

mysql> SELECT JSON_SEARCH(@j, 'one', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'one', 'abc') |
+-------------------------------+
| "$[0]"                        |
+-------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'abc') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+

若搜索内容不存在，则返回NULL

mysql> SELECT JSON_SEARCH(@j, 'all', 'ghi');
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'ghi') |
+-------------------------------+
| NULL                          |
+-------------------------------+

查询值为10所在的路径

mysql> SELECT JSON_SEARCH(@j, 'all', '10');
+------------------------------+
| JSON_SEARCH(@j, 'all', '10') |
+------------------------------+
| "$[1][0].k"                  |
+------------------------------+

指定JSON搜索路径

@j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$');
+-----------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$') |
+-----------------------------------------+
| "$[1][0].k"                             |
+-----------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]');
+--------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*]') |
+--------------------------------------------+
| "$[1][0].k"                                |
+--------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$**.k') |
+---------------------------------------------+
| "$[1][0].k"                                 |
+---------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k');
+-------------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k') |
+-------------------------------------------------+
| "$[1][0].k"                                     |
+-------------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]');
+--------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1]') |
+--------------------------------------------+
| "$[1][0].k"                                |
+--------------------------------------------+

#指定搜索路径为数组中第二个元素内的第一个元素
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]');
+-----------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]') |
+-----------------------------------------------+
| "$[1][0].k"                                   |
+-----------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x"                                    |
+---------------------------------------------+

模糊匹配

@j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

mysql> SELECT JSON_SEARCH(@j, 'all', '%a%');
+-------------------------------+
| JSON_SEARCH(@j, 'all', '%a%') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%');
+-------------------------------+
| JSON_SEARCH(@j, 'all', '%b%') |
+-------------------------------+
| ["$[0]", "$[2].x", "$[3].y"]  |
+-------------------------------+

# 指定搜索路径，$[0] = "abc"
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]') |
+---------------------------------------------+
| "$[0]"                                      |
+---------------------------------------------+

# $[2] = {"x":"abc"}
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x"                                    |
+---------------------------------------------+

# $[1] = [{"k": "10"}, "def"] 模糊匹配无结果
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]') |
+---------------------------------------------+
| NULL                                        |
+---------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');
+-------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', '', '$[1]') |
+-------------------------------------------+
| NULL                                      |
+-------------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');
+-------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') |
+-------------------------------------------+
| "$[3].y"                                  |
+-------------------------------------------+

日常示例

在日常开发过程中如果需要使用JSON_SEARCH查询的话，需要搭配IS NOT NULL来获取符合条件的数据

创建用户表

Create Table: CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL COMMENT '名字',
  `age` int(4) unsigned NOT NULL COMMENT '年龄',
  `info` text COMMENT '补充信息',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表'

插入用户数据

INSERT INTO `suhw`.`user` (`name`, `age`, `info`) VALUES ('suhw', '23', '{"phone":"12312123434","language":["c++","java","go"]}');
INSERT INTO `suhw`.`user` (`name`, `age`, `info`) VALUES ('bob', '20', '{"phone":"18912123434","language":["c++","c","go","php"]}');

查询会go语言的用户

mysql> select * from user where JSON_SEARCH(info, 'all', 'go', NULL, '$.language') IS NOT NULL\G
*************************** 1. row ***************************
  id: 1
name: suhw
 age: 23
info: {"phone":"12312123434","language":["c++","java","go"]}
*************************** 2. row ***************************
  id: 2
name: bob
 age: 20
info: {"phone":"18912123434","language":["c++","c","go","php"]}
2 rows in set (0.00 sec)

参考

https://www.docs4dev.com/docs/zh/mysql/5.7/reference/json-search-functions.html#function_json-search