MySQL高级函数之：`JSON_CONTAINS_PATH()`：其在判断`JSON`路径是否存在时的应用。

MySQL JSON_CONTAINS_PATH() 函数详解：判断 JSON 路径是否存在

大家好！今天我们来深入探讨 MySQL 中一个非常实用的 JSON 函数：JSON_CONTAINS_PATH()。这个函数专门用于判断 JSON 文档中是否存在特定的路径，在处理 JSON 数据时，它能帮助我们进行有效的条件判断和数据提取。

1. JSON_CONTAINS_PATH() 函数的基本语法

JSON_CONTAINS_PATH() 函数的基本语法如下：

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

• json_doc: 这是一个包含 JSON 数据的字符串或 JSON 类型的列。它是我们要检查的目标 JSON 文档。
• one_or_all: 这个参数指定了如何处理多个 path 参数。它可以是以下两个值之一：
  • 'one': 只要至少有一个 path 存在于 json_doc 中，函数就返回 1。
  • 'all': 只有当所有的 path 都存在于 json_doc 中，函数才返回 1。
• path: 一个或多个 JSON 路径表达式，用于指定要检查的路径。路径表达式使用 $ 表示 JSON 文档的根，并使用 . 或 [] 来访问 JSON 对象的属性或数组的元素。

函数返回值为：

• 1 (TRUE): 如果指定的条件满足 (至少一个路径存在或所有路径都存在，取决于 one_or_all 的值)。
• 0 (FALSE): 如果指定的条件不满足。
• NULL: 如果任何输入参数为 NULL。

2. 路径表达式的构成

理解 JSON 路径表达式是使用 JSON_CONTAINS_PATH() 的关键。以下是一些常见的路径表达式示例：

• $.name: 访问 JSON 文档根对象中的 name 属性。
• $.address.city: 访问 JSON 文档根对象中的 address 属性下的 city 属性。
• $.phoneNumbers[0].type: 访问 JSON 文档根对象中的 phoneNumbers 数组的第一个元素的 type 属性。
• *`$.orders[].productName**: 访问 JSON 文档根对象中的orders数组中所有元素的productName` 属性（MySQL 5.7.22 及更高版本支持）。
• $."first name": 如果属性名包含空格或其他特殊字符，需要用双引号括起来。

3. one 和 all 的区别及应用场景

one_or_all 参数决定了多个路径的判断逻辑。

3.1. one 的应用场景

如果只需要检查是否存在至少一个路径，就使用 'one'。例如，假设我们有一个存储用户信息的 JSON 文档，我们想知道文档中是否包含用户的姓名或邮箱信息。

SELECT JSON_CONTAINS_PATH(
    '{"name": "Alice", "age": 30}',
    'one',
    '$.name',
    '$.email'
); -- 返回 1，因为 'name' 存在

SELECT JSON_CONTAINS_PATH(
    '{"age": 30, "city": "New York"}',
    'one',
    '$.name',
    '$.email'
); -- 返回 0，因为 'name' 和 'email' 都不存在

3.2. all 的应用场景

如果需要确保所有路径都存在，就使用 'all'。例如，假设我们需要验证一个订单 JSON 文档是否同时包含 orderId 和 customerId。

SELECT JSON_CONTAINS_PATH(
    '{"orderId": 123, "customerId": 456, "total": 100}',
    'all',
    '$.orderId',
    '$.customerId'
); -- 返回 1，因为 'orderId' 和 'customerId' 都存在

SELECT JSON_CONTAINS_PATH(
    '{"orderId": 123, "total": 100}',
    'all',
    '$.orderId',
    '$.customerId'
); -- 返回 0，因为 'customerId' 不存在

4. 结合表数据进行实际应用

假设我们有一个名为 products 的表，其中包含一个名为 details 的 JSON 列，用于存储产品详细信息。

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    details JSON
);

INSERT INTO products (name, details) VALUES
('Laptop', '{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "i7", "ram": "16GB"}}'),
('Mouse', '{"brand": "Logitech", "model": "MX Master 3", "features": ["wireless", "ergonomic"]}'),
('Keyboard', '{"brand": "Corsair", "model": "K95 RGB", "type": "mechanical"}');

4.1. 查询包含特定品牌的产品

我们可以使用 JSON_CONTAINS_PATH() 来查找 details 列中包含 brand 属性的产品。

SELECT id, name
FROM products
WHERE JSON_CONTAINS_PATH(details, 'one', '$.brand');

这个查询会返回所有 details 列中包含 brand 属性的产品的 id 和 name。

4.2. 查询包含特定型号和品牌的产品

如果我们需要同时检查 brand 和 model 属性是否存在，可以使用 'all'。

SELECT id, name
FROM products
WHERE JSON_CONTAINS_PATH(details, 'all', '$.brand', '$.model');

这个查询会返回所有 details 列中同时包含 brand 和 model 属性的产品的 id 和 name。

4.3. 查询包含特定规格的产品 (嵌套 JSON)

对于嵌套的 JSON 数据，我们可以使用更复杂的路径表达式。例如，要查找 specs 中包含 cpu 属性的 Laptop 产品，可以这样写：

SELECT id, name
FROM products
WHERE name = 'Laptop' AND JSON_CONTAINS_PATH(details, 'one', '$.specs.cpu');

4.4. 查询包含特定数组元素的产品

如果 JSON 文档包含数组，我们可以使用索引来访问数组元素。例如，要查找 features 数组中包含 "wireless" 的 Mouse 产品，需要先使用 JSON_CONTAINS() 函数，因为JSON_CONTAINS_PATH()无法直接判断数组元素的值。

SELECT id, name
FROM products
WHERE name = 'Mouse' AND JSON_CONTAINS(details, '"wireless"', '$.features');

4.5. 结合 JSON_EXTRACT() 进行更精确的匹配

虽然 JSON_CONTAINS_PATH() 可以判断路径是否存在，但它无法判断路径对应的值是否符合要求。为了进行更精确的匹配，我们可以结合 JSON_EXTRACT() 函数。

例如，要查找品牌为 "Dell" 的产品，可以这样写：

SELECT id, name
FROM products
WHERE JSON_CONTAINS_PATH(details, 'one', '$.brand')
  AND JSON_EXTRACT(details, '$.brand') = '"Dell"';

注意，JSON_EXTRACT() 返回的是带引号的字符串，所以我们需要在比较时加上引号。另一种写法是使用 ->> 运算符，它可以自动去除引号：

SELECT id, name
FROM products
WHERE JSON_CONTAINS_PATH(details, 'one', '$.brand')
  AND details->>'$.brand' = 'Dell';

5. NULL 值的处理

如果 json_doc 或任何一个 path 参数为 NULL，JSON_CONTAINS_PATH() 函数会返回 NULL。 这点需要特别注意，因为 NULL 在 SQL 中通常被视为未知值，可能会导致意外的结果。

例如：

SELECT JSON_CONTAINS_PATH(NULL, 'one', '$.name'); -- 返回 NULL
SELECT JSON_CONTAINS_PATH('{"name": "Alice"}', 'one', NULL); -- 返回 NULL

为了避免 NULL 值带来的问题，可以使用 COALESCE() 函数来处理可能的 NULL 值。

SELECT id, name
FROM products
WHERE COALESCE(JSON_CONTAINS_PATH(details, 'one', '$.brand'), 0) = 1;

在这个例子中，如果 details 列为 NULL，JSON_CONTAINS_PATH() 会返回 NULL，COALESCE() 函数会将 NULL 转换为 0，从而避免了 NULL 值的影响。

6. 性能考量

在使用 JSON_CONTAINS_PATH() 函数时，需要注意性能问题。由于 JSON 操作通常比较耗费资源，频繁使用 JSON_CONTAINS_PATH() 可能会影响查询性能。

以下是一些优化建议：

• 建立索引: 如果在经常用于 JSON 查询的列上建立索引，可以显著提高查询性能。但是，MySQL 5.7 之前不支持 JSON 列的索引。MySQL 5.7 之后，可以使用虚拟列和前缀索引来间接实现 JSON 列的索引。 MySQL 8.0 之后，开始原生支持 JSON 索引。
• 避免全表扫描: 尽量使用其他条件来缩小查询范围，避免对整个表进行扫描。
• 优化 JSON 文档结构: 合理设计 JSON 文档的结构，避免过深的嵌套和过大的文档，可以提高 JSON 操作的效率。
• *谨慎使用 `通配符**: 在路径表达式中使用*` 通配符可能会导致性能下降，尽量避免使用。

7. 实际案例演示

现在我们来演示一个更完整的实际案例。假设我们有一个 users 表，其中包含一个 preferences 的 JSON 列，用于存储用户的个性化设置。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255),
    preferences JSON
);

INSERT INTO users (username, preferences) VALUES
('Alice', '{"theme": "dark", "notifications": {"email": true, "sms": false}}'),
('Bob', '{"theme": "light", "notifications": {"email": false, "sms": true}, "language": "en"}'),
('Charlie', '{"notifications": {"email": true}, "fontSize": "16px"}');

7.1. 查询开启了邮件通知的用户

SELECT id, username
FROM users
WHERE JSON_CONTAINS_PATH(preferences, 'one', '$.notifications.email')
  AND preferences->>'$.notifications.email' = 'true';

7.2. 查询设置了主题的用户

SELECT id, username
FROM users
WHERE JSON_CONTAINS_PATH(preferences, 'one', '$.theme');

7.3. 查询既设置了主题又设置了语言的用户

SELECT id, username
FROM users
WHERE JSON_CONTAINS_PATH(preferences, 'all', '$.theme', '$.language');

7.4. 查询未设置短信通知的用户

这里需要注意的是，如果某个用户没有 notifications 属性，或者 notifications 属性没有 sms 属性，JSON_CONTAINS_PATH() 都会返回 0。 因此，我们需要使用 NOT JSON_CONTAINS_PATH() 或者结合 JSON_EXTRACT() 来实现。

SELECT id, username
FROM users
WHERE NOT JSON_CONTAINS_PATH(preferences, 'one', '$.notifications.sms');

或者使用以下方式：

SELECT id, username
FROM users
WHERE NOT JSON_CONTAINS(preferences, 'true', '$.notifications.sms')
  AND NOT JSON_CONTAINS(preferences, 'false', '$.notifications.sms')
  AND JSON_CONTAINS_PATH(preferences, 'one', '$.notifications');

8. 总结

JSON_CONTAINS_PATH() 函数是 MySQL 中一个强大的 JSON 处理工具，可以帮助我们判断 JSON 文档中是否存在指定的路径。通过灵活运用 one_or_all 参数和路径表达式，我们可以实现各种复杂的条件判断。在使用时，需要注意 NULL 值的处理和性能问题，并结合其他 JSON 函数（如 JSON_EXTRACT() 和 JSON_CONTAINS()）来实现更精确的匹配。