MySQL - json_search 小结

╰+攻爆jí腚メ 2022-12-09 15:51 153阅读 0赞

在开发过程中遇到某个表中采用`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"}]';

1.  对比`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"]            |
    +-------------------------------+

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

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

1.  查询值为10所在的路径

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

1.  指定`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"                                    |
    +---------------------------------------------+

1.  模糊匹配

`@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