MySQL高级函数之：`JSON_SEARCH()`：其在`JSON`文档中查找指定字符串时的路径返回。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL高级函数之：JSON_SEARCH()：路径查找利器

大家好，今天我们来深入探讨MySQL中的一个非常有用的JSON函数：JSON_SEARCH()。这个函数允许我们在JSON文档中查找特定的字符串，并返回匹配路径，为复杂JSON数据的查询和分析提供了强大的支持。

1. JSON_SEARCH() 函数概述

JSON_SEARCH() 函数用于在 JSON 文档中查找指定的字符串，并返回匹配该字符串的路径。如果找到了多个匹配项，它会返回第一个匹配项的路径。如果没有找到任何匹配项，则返回 NULL。

语法:

JSON_SEARCH(json_doc, one_or_all, search_string[, escape_char[, path ...]])

参数说明:

json_doc: 要搜索的 JSON 文档。它可以是一个包含 JSON 值的列，或者一个 JSON 字符串。
one_or_all: 指定返回单个匹配项还是所有匹配项。
- 'one' (默认): 返回第一个匹配项的路径。
- 'all' : 返回所有匹配项的路径，以 JSON 数组的形式返回。
search_string: 要搜索的字符串。可以使用 % 和 _ 作为通配符进行模糊搜索。
escape_char: 用于转义通配符的字符。如果未指定，则默认为反斜杠 ()。
path: 可选参数。指定要搜索的 JSON 文档的特定路径。如果未指定，则搜索整个文档。

返回值:

如果找到匹配项，则返回匹配项的路径（或路径数组，如果 one_or_all 为 'all'）。
如果没有找到匹配项，则返回 NULL。

2. 基础用法示例

我们先创建一个简单的表，用于演示 JSON_SEARCH() 函数的使用。

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_data JSON
);

INSERT INTO employees (employee_data) VALUES
('{"name": "Alice", "age": 30, "city": "New York"}'),
('{"name": "Bob", "age": 25, "city": "London"}'),
('{"name": "Charlie", "age": 35, "city": "Paris"}'),
('{"name": "David", "age": 40, "city": "New York", "skills": ["Java", "Python", "SQL"]}');

示例 1: 查找名为 "Alice" 的员工

SELECT id, JSON_SEARCH(employee_data, 'one', 'Alice') AS path
FROM employees
WHERE JSON_SEARCH(employee_data, 'one', 'Alice') IS NOT NULL;

输出:

+----+--------+
| id | path   |
+----+--------+
|  1 | $.name |
+----+--------+

这个查询返回了 id 为 1 的员工的 name 属性的路径 $.name，因为它包含了字符串 "Alice"。

示例 2: 查找年龄为 25 的员工

SELECT id, JSON_SEARCH(employee_data, 'one', '25') AS path
FROM employees
WHERE JSON_SEARCH(employee_data, 'one', '25') IS NOT NULL;

输出:

+----+--------+
| id | path   |
+----+--------+
|  2 | $.age  |
+----+--------+

示例 3: 查找城市为 "New York" 的员工

SELECT id, JSON_SEARCH(employee_data, 'one', 'New York') AS path
FROM employees
WHERE JSON_SEARCH(employee_data, 'one', 'New York') IS NOT NULL;

输出:

+----+----------+
| id | path     |
+----+----------+
|  1 | $.city   |
|  4 | $.city   |
+----+----------+

3. 使用 ‘all’ 参数返回所有匹配项

如果我们需要查找 JSON 文档中所有匹配的路径，可以使用 'all' 参数。

示例 4: 查找包含 "York" 的城市的所有路径

SELECT id, JSON_SEARCH(employee_data, 'all', '%York%') AS paths
FROM employees
WHERE JSON_SEARCH(employee_data, 'all', '%York%') IS NOT NULL;

输出:

+----+------------------------+
| id | paths                  |
+----+------------------------+
|  1 | ["$.city"]             |
|  4 | ["$.city"]             |
+----+------------------------+

注意，当使用 'all' 参数时，返回值是一个 JSON 数组，即使只有一个匹配项。

示例 5: 查找包含 "Java" 的所有路径

SELECT id, JSON_SEARCH(employee_data, 'all', 'Java') AS paths
FROM employees
WHERE JSON_SEARCH(employee_data, 'all', 'Java') IS NOT NULL;

输出:

+----+--------------------+
| id | paths              |
+----+--------------------+
|  4 | ["$.skills[0]"] |
+----+--------------------+

4. 使用路径参数缩小搜索范围

path 参数允许我们指定要搜索的 JSON 文档的特定部分。这可以提高搜索效率，并避免不必要的匹配。

示例 6: 在 skills 数组中查找 "Python"

SELECT id, JSON_SEARCH(employee_data, 'one', 'Python', NULL, '$.skills') AS path
FROM employees
WHERE JSON_SEARCH(employee_data, 'one', 'Python', NULL, '$.skills') IS NOT NULL;

输出:

+----+-------------+
| id | path        |
+----+-------------+
|  4 | $.skills[1] |
+----+-------------+

在这个例子中，我们只在 employee_data 的 skills 数组中搜索 "Python"，而不是搜索整个文档。

示例 7: 在 skills 数组中查找所有包含 "a" 的元素

SELECT id, JSON_SEARCH(employee_data, 'all', '%a%', NULL, '$.skills') AS paths
FROM employees
WHERE JSON_SEARCH(employee_data, 'all', '%a%', NULL, '$.skills') IS NOT NULL;

输出:

+----+------------------------+
| id | paths                  |
+----+------------------------+
|  4 | ["$.skills[0]"]             |
+----+------------------------+

5. 使用转义字符

如果 search_string 包含通配符 (% 或 _)，并且我们想要将其作为字面字符进行搜索，则需要使用转义字符。

示例 8: 查找包含百分号 (%) 的字符串

假设我们的数据中包含如下记录：

INSERT INTO employees (employee_data) VALUES ('{"discount": "10%"}');

如果我们想要查找包含 "10%" 的记录，我们需要转义百分号。

SELECT id, JSON_SEARCH(employee_data, 'one', '10\%') AS path
FROM employees
WHERE JSON_SEARCH(employee_data, 'one', '10\%') IS NOT NULL;

输出:

+----+------------+
| id | path       |
+----+------------+
|  5 | $.discount |
+----+------------+

在这个例子中，我们使用反斜杠 () 作为转义字符来转义百分号。如果我们使用其他的转义字符，则需要将其作为第四个参数传递给 JSON_SEARCH() 函数。

示例 9: 自定义转义字符

假设我们想使用 $ 作为转义字符。

SELECT id, JSON_SEARCH(employee_data, 'one', '10$%', '$') AS path
FROM employees
WHERE JSON_SEARCH(employee_data, 'one', '10$%', '$') IS NOT NULL;

6. 与JSON_EXTRACT() 结合使用

JSON_SEARCH() 返回的是路径，而 JSON_EXTRACT() 可以根据路径提取 JSON 文档中的值。将这两个函数结合使用可以实现更复杂的数据提取和过滤。

示例 10: 提取包含 "Alice" 的员工的年龄

SELECT
    id,
    JSON_EXTRACT(employee_data, JSON_SEARCH(employee_data, 'one', 'Alice')) AS age
FROM
    employees
WHERE
    JSON_SEARCH(employee_data, 'one', 'Alice') IS NOT NULL;

输出:

+----+------+
| id | age  |
+----+------+
|  1 | "Alice" |
+----+------+

注意这里实际上提取的是 "Alice" 这个字符串，因为我们搜索的是 "Alice" 这个字符串所在的路径。为了提取年龄，我们需要修改查询。

SELECT
    id,
    JSON_EXTRACT(employee_data, '$.age') AS age
FROM
    employees
WHERE
    JSON_SEARCH(employee_data, 'one', 'Alice') IS NOT NULL;

输出:

+----+------+
| id | age  |
+----+------+
|  1 | 30   |
+----+------+

示例 11: 提取包含 "Java" 技能的员工的姓名

SELECT
    id,
    JSON_EXTRACT(employee_data, '$.name') AS name
FROM
    employees
WHERE
    JSON_SEARCH(employee_data, 'one', 'Java', NULL, '$.skills') IS NOT NULL;

输出:

+----+-------+
| id | name  |
+----+-------+
|  4 | David |
+----+-------+

7. 注意事项和最佳实践

性能: JSON_SEARCH() 函数的性能取决于 JSON 文档的大小和复杂性。在大型 JSON 文档上进行搜索可能会比较慢。考虑使用索引来优化查询性能。
通配符: 使用通配符进行模糊搜索可能会降低性能。尽量避免在大型 JSON 文档上使用通配符。
路径: 使用 path 参数可以缩小搜索范围，提高查询效率。
NULL 值: 如果 json_doc 为 NULL，则 JSON_SEARCH() 函数返回 NULL。
大小写敏感性: JSON_SEARCH() 函数默认情况下是大小写敏感的。如果需要进行大小写不敏感的搜索，可以使用 LOWER() 或 UPPER() 函数将 json_doc 和 search_string 都转换为小写或大写。
错误处理: 如果 json_doc 不是有效的 JSON 文档，则 JSON_SEARCH() 函数可能会返回错误。建议在使用 JSON_SEARCH() 函数之前验证 JSON 文档的有效性。
数据类型: JSON_SEARCH 搜索的是字符串，即使JSON文档中的值是数字或者其他类型，也会被当做字符串来比较。需要注意类型转换的问题。

8. JSON_SEARCH()与其他JSON函数的比较

函数	功能	返回值	适用场景
`JSON_SEARCH()`	在JSON文档中查找指定字符串，返回路径	匹配字符串的路径（或路径数组）	查找特定字符串在JSON文档中的位置，例如查找包含特定关键词的属性。
`JSON_EXTRACT()`	从JSON文档中提取指定路径的值	指定路径的值	根据已知的路径提取JSON文档中的值，例如提取特定属性的值。
`JSON_CONTAINS()`	检查JSON文档是否包含指定的JSON值	1 (包含) 或 0 (不包含)	检查JSON文档中是否存在特定的JSON对象或数组，例如检查是否存在某个特定的键值对。
`JSON_KEYS()`	返回JSON对象的键的数组	JSON数组，包含JSON对象的键	获取JSON对象的所有键，例如获取一个JSON对象的所有属性名。
`JSON_LENGTH()`	返回JSON文档的长度	整数，表示JSON文档的长度（元素个数）	获取JSON数组的长度或JSON对象的键值对数量，例如统计一个数组有多少个元素。

表格：JSON_SEARCH() 参数总结

参数	数据类型	必选	描述
`json_doc`	JSON	是	要搜索的JSON文档。
`one_or_all`	VARCHAR	是	`'one'` (返回第一个匹配项) 或 `'all'` (返回所有匹配项)。
`search_string`	VARCHAR	是	要搜索的字符串。可以使用 `%` 和 `_` 作为通配符。
`escape_char`	VARCHAR	否	用于转义通配符的字符。默认为反斜杠 ()。
`path`	VARCHAR	否	可选参数。指定要搜索的JSON文档的特定路径。如果未指定，则搜索整个文档。可以指定多个路径，例如 `$.a`, `$.b`, `$.c`。

9. 实际应用场景

日志分析: 在存储 JSON 格式的日志数据时，可以使用 JSON_SEARCH() 函数来查找包含特定错误消息或事件的日志记录。
配置管理: 在存储 JSON 格式的配置信息时，可以使用 JSON_SEARCH() 函数来查找包含特定配置项的配置信息。
数据清洗: 可以使用 JSON_SEARCH() 函数来查找包含无效数据或错误数据的 JSON 文档。
权限控制: 可以结合权限数据（例如用户角色和权限）存储在 JSON 格式的数据中，使用 JSON_SEARCH 来查找用户是否具有特定权限。
电商网站商品搜索：商品属性以JSON存储，用户通过关键词搜索，可以用 JSON_SEARCH 找到包含关键词的商品。

JSON_SEARCH函数提供了强大的路径查找能力，在实际开发中善用此函数可以简化JSON数据的处理逻辑。
希望今天的讲解能够帮助大家更好地理解和使用 JSON_SEARCH() 函数。

路径查找，精确搜索，JSON_SEARCH是你的好帮手。
灵活运用，优化查询，让数据处理更高效。
感谢大家的聆听，下次再见！

MySQL高级函数之：JSON_SEARCH()：路径查找利器

发表回复 取消回复

发表回复取消回复