MySQL高级函数之：JSON_CONTAINS_PATH()：判断JSON路径是否存在
大家好，今天我们来深入探讨MySQL中一个非常有用的JSON函数：JSON_CONTAINS_PATH()。 尤其是在处理复杂JSON数据时，该函数能大大简化我们的查询逻辑，提高效率。 本次讲座将围绕该函数的语法、参数、返回值以及实际应用场景进行详细讲解，并通过丰富的示例帮助大家理解和掌握。
1. JSON_CONTAINS_PATH() 函数概述
JSON_CONTAINS_PATH() 函数用于检查JSON文档中是否存在指定的路径。 它可以验证一个或多个路径是否存在于给定的JSON文档中。 如果所有指定的路径都存在，则函数返回 1；否则，返回 0。
2. JSON_CONTAINS_PATH() 函数语法
JSON_CONTAINS_PATH(json_doc, one_or_all, path1[, path2 ...])
各参数含义如下：
json_doc: 要进行检查的 JSON 文档。它可以是 JSON 类型的列，也可以是包含 JSON 数据的字符串。one_or_all: 一个字符串参数，用于指定如何处理多个路径。它可以是以下两个值之一：'one': 如果至少有一个指定的路径存在，则返回 1。'all': 只有所有指定的路径都存在，才返回 1。
path1, path2, ...: 一个或多个 JSON 路径表达式，用于指定要检查的路径。
3. JSON_CONTAINS_PATH() 函数返回值
JSON_CONTAINS_PATH() 函数返回一个整数值：
1: 如果符合one_or_all参数的条件，即至少一个路径存在（one）或所有路径都存在（all）。0: 如果不符合one_or_all参数的条件。NULL: 如果任何参数为NULL，或者json_doc不是有效的 JSON 文档。
4. JSON路径表达式
在 JSON_CONTAINS_PATH() 函数中，路径表达式用于指定 JSON 文档中的特定位置。 MySQL 支持以下路径表达式：
'$': 表示 JSON 文档的根节点。.key: 表示具有给定键的对象成员。例如，$.name表示根对象中的name键对应的值。[index]: 表示数组中的指定索引处的元素。索引从 0 开始。例如，$[0]表示根数组的第一个元素。[*]：通配符，表示数组中的所有元素。 例如，$.items[*].price表示items数组中所有元素的price属性。.**: 深度通配符，表示递归地匹配所有层级的成员。
5. 示例演示
为了更好地理解 JSON_CONTAINS_PATH() 函数，我们通过一些具体的示例来进行演示。
5.1 创建测试表和数据
首先，我们创建一个名为 products 的表，其中包含一个 JSON 类型的列 details：
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
details JSON
);然后，我们插入一些数据：
INSERT INTO products (name, details) VALUES
('Product A', '{"price": 25.99, "description": "A high-quality product", "features": ["durable", "reliable"]}'),
('Product B', '{"price": 49.99, "category": "Electronics", "dimensions": {"width": 10, "height": 5}}'),
('Product C', '{"description": "An affordable option", "options": {"color": "blue", "size": "M"}}'),
('Product D', NULL),
('Product E', '{"price":99.99,"discount":0.1,"features":["waterproof","portable"]}');5.2 基本用法示例
-
检查
price字段是否存在：SELECT id, name, JSON_CONTAINS_PATH(details, 'one', '$.price') AS has_price FROM products;结果：
id name has_price 1 Product A 1 2 Product B 1 3 Product C 0 4 Product D NULL 5 Product E 1 这个查询检查每个产品的
detailsJSON 文档中是否存在price字段。 -
检查
category字段是否存在：SELECT id, name, JSON_CONTAINS_PATH(details, 'one', '$.category') AS has_category FROM products;结果：
id name has_category 1 Product A 0 2 Product B 1 3 Product C 0 4 Product D NULL 5 Product E 0 -
检查
price和description字段是否都存在：SELECT id, name, JSON_CONTAINS_PATH(details, 'all', '$.price', '$.description') AS has_price_and_description FROM products;结果：
id name has_price_and_description 1 Product A 1 2 Product B 0 3 Product C 0 4 Product D NULL 5 Product E 0 只有
Product A同时具有price和description字段，所以结果为 1。 -
检查
price或category字段是否存在：SELECT id, name, JSON_CONTAINS_PATH(details, 'one', '$.price', '$.category') AS has_price_or_category FROM products;结果：
id name has_price_or_category 1 Product A 1 2 Product B 1 3 Product C 0 4 Product D NULL 5 Product E 1 只要
price或category字段存在，结果就为 1。
5.3 嵌套 JSON 示例
-
检查
dimensions对象中是否存在width字段：SELECT id, name, JSON_CONTAINS_PATH(details, 'one', '$.dimensions.width') AS has_width FROM products;结果：
id name has_width 1 Product A 0 2 Product B 1 3 Product C 0 4 Product D NULL 5 Product E 0 -
检查
options对象中是否存在color和size字段：SELECT id, name, JSON_CONTAINS_PATH(details, 'all', '$.options.color', '$.options.size') AS has_color_and_size FROM products;结果：
id name has_color_and_size 1 Product A 0 2 Product B 0 3 Product C 1 4 Product D NULL 5 Product E 0
5.4 数组 JSON 示例
-
检查
features数组中是否存在任何元素： (这个例子不能直接使用JSON_CONTAINS_PATH，JSON_CONTAINS_PATH主要用来检查路径是否存在，而不是判断数组是否为空。如果要检查数组是否为空，需要借助其他函数，例如JSON_LENGTH())我们可以使用
JSON_LENGTH()函数来判断features数组的长度是否大于0。SELECT id, name, (JSON_LENGTH(details->'$.features') > 0) AS has_features FROM products;结果：
id name has_features 1 Product A 1 2 Product B 0 3 Product C 0 4 Product D NULL 5 Product E 1 -
*使用通配符 `[]
检查features数组中的元素是否包含特定的值** (这个例子不能直接使用JSON_CONTAINS_PATH，JSON_CONTAINS_PATH主要用来检查路径是否存在，而无法直接检查数组元素的值。我们需要使用JSON_CONTAINS` 函数。)SELECT id, name, JSON_CONTAINS(details->'$.features', '"durable"') AS has_durable_feature FROM products;结果：
id name has_durable_feature 1 Product A 1 2 Product B 0 3 Product C 0 4 Product D NULL 5 Product E 0
5.5 NULL 值处理
如果 json_doc 参数为 NULL，则 JSON_CONTAINS_PATH() 函数返回 NULL。如上面的示例所示。
6. JSON_CONTAINS_PATH() 函数的应用场景
JSON_CONTAINS_PATH() 函数在以下场景中非常有用：
- 数据验证: 在插入或更新数据之前，可以使用
JSON_CONTAINS_PATH()函数来验证 JSON 文档是否包含必要的字段。 - 条件查询: 可以使用
JSON_CONTAINS_PATH()函数来过滤包含特定字段的 JSON 文档。 - 数据转换: 可以使用
JSON_CONTAINS_PATH()函数来确定如何转换 JSON 文档，具体取决于其包含的字段。 - 动态报表生成: 根据JSON文档中字段的存在与否，动态生成不同的报表。
- API数据处理: 在接收外部API返回的JSON数据时，可以使用该函数快速判断是否存在某个关键字段，从而进行下一步处理。
7. 注意事项
JSON_CONTAINS_PATH()函数只能检查路径是否存在，而不能检查路径对应的值是否符合特定的条件。- 路径表达式必须是有效的，否则函数将返回错误。
- 性能方面，避免在大型JSON文档中使用过于复杂的路径表达式，这可能会影响查询效率。 尽量使用简单的路径，并考虑使用索引来优化查询。
8. 与其他JSON函数的比较
JSON_CONTAINS()vsJSON_CONTAINS_PATH():JSON_CONTAINS()用于检查 JSON 文档是否包含指定的 JSON 片段，而JSON_CONTAINS_PATH()用于检查 JSON 文档中是否存在指定的路径。JSON_EXTRACT()vsJSON_CONTAINS_PATH():JSON_EXTRACT()用于提取 JSON 文档中指定路径的值，而JSON_CONTAINS_PATH()用于检查路径是否存在。
| 函数 | 功能 | 返回值 | 示例 |
|---|---|---|---|
JSON_CONTAINS() |
检查JSON文档是否包含指定的JSON片段 | 1 (包含), 0 (不包含), NULL (参数为NULL或JSON无效) | JSON_CONTAINS(details->'$.features', '"durable"') |
JSON_CONTAINS_PATH() |
检查JSON文档是否存在指定的路径 | 1 (存在), 0 (不存在), NULL (参数为NULL或JSON无效) | JSON_CONTAINS_PATH(details, 'one', '$.price') |
JSON_EXTRACT() |
从JSON文档中提取指定路径的值 | 提取的值，NULL (路径不存在或参数为NULL) | JSON_EXTRACT(details, '$.price') |
JSON_LENGTH() |
返回JSON文档的长度 (如果是数组，返回元素个数；如果是对象，返回键值对个数) | 整数，NULL (参数为NULL) | JSON_LENGTH(details->'$.features') |
9. 实际案例分析
假设我们有一个电商平台，需要根据商品的不同属性进行筛选。商品的属性信息存储在 JSON 类型的 attributes 列中。
CREATE TABLE products_v2 (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
attributes JSON
);
INSERT INTO products_v2 (name, attributes) VALUES
('Laptop', '{"brand": "Dell", "screen_size": 15.6, "memory": "16GB", "storage": "512GB SSD"}'),
('Smartphone', '{"brand": "Samsung", "screen_size": 6.5, "camera": "108MP", "storage": "128GB"}'),
('Tablet', '{"brand": "Apple", "screen_size": 11, "memory": "8GB", "storage": "256GB"}'),
('Smartwatch', '{"brand": "Fitbit", "features": ["heart rate monitor", "sleep tracking"], "battery_life": "7 days"}');现在，我们需要查询所有具有 "memory" 属性的商品：
SELECT id, name
FROM products_v2
WHERE JSON_CONTAINS_PATH(attributes, 'one', '$.memory');结果：
| id | name |
|---|---|
| 1 | Laptop |
| 3 | Tablet |
我们还可以查询同时具有 "memory" 和 "storage" 属性的商品：
SELECT id, name
FROM products_v2
WHERE JSON_CONTAINS_PATH(attributes, 'all', '$.memory', '$.storage');结果：
| id | name |
|---|---|
| 1 | Laptop |
| 3 | Tablet |
这些例子展示了 JSON_CONTAINS_PATH() 函数在实际应用中的强大功能。
10. 优化建议
- 索引: 如果经常需要根据 JSON 文档中的特定路径进行查询，可以考虑在 JSON 列上创建索引。MySQL 5.7.9 及更高版本支持在 JSON 列上创建虚拟列索引，可以显著提高查询效率。
- 简化路径表达式: 尽量使用简单的路径表达式，避免在大型 JSON 文档中使用过于复杂的路径表达式。
- 缓存: 对于不经常变化的 JSON 数据，可以考虑使用缓存来减少数据库的查询压力。
总结本次内容
JSON_CONTAINS_PATH() 函数是MySQL中一个强大的JSON函数，可以方便地检查JSON文档中是否存在指定的路径。 掌握该函数可以简化查询逻辑，提高开发效率。 在实际应用中，需要根据具体场景选择合适的路径表达式，并注意优化查询性能。 通过本次讲座的学习，相信大家已经对 JSON_CONTAINS_PATH() 函数有了更深入的理解，并能够在实际项目中灵活运用。