如何利用`JSON_CONTAINS()`函数进行`JSON`数据的快速查询？

JSON_CONTAINS() 函数在 JSON 数据快速查询中的应用

大家好，今天我们来深入探讨 MySQL 中 JSON_CONTAINS() 函数，以及如何利用它来高效地查询和过滤 JSON 数据。在现代应用开发中，JSON 作为一种轻量级的数据交换格式被广泛使用。MySQL 自 5.7 版本开始支持 JSON 数据类型，并提供了一系列用于操作 JSON 数据的函数。JSON_CONTAINS() 就是其中一个非常重要的函数，它允许我们判断一个 JSON 文档是否包含特定的 JSON 片段。

1. JSON_CONTAINS() 函数的基本语法和功能

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

JSON_CONTAINS(json_doc, target, [path])

• json_doc: 要进行查询的 JSON 文档。它可以是 JSON 类型的列，也可以是一个包含 JSON 数据的字符串。
• target: 要查找的 JSON 片段。它可以是一个 JSON 对象、数组、或者一个简单的标量值（例如字符串、数字、布尔值）。
• path: (可选) JSON 文档中进行查找的路径。如果指定了路径，则只会在该路径下的内容中查找 target。路径使用 $ 表示文档根，. 表示对象成员，[] 表示数组索引。

JSON_CONTAINS() 函数返回 1 (TRUE) 如果 json_doc 包含 target，返回 0 (FALSE) 如果不包含，返回 NULL 如果任何参数为 NULL。

示例：

假设我们有一个名为 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": "Intel i7", "ram": "16GB", "storage": "512GB SSD"}}'),
('Monitor', '{"brand": "LG", "model": "27UL850", "resolution": "4K"}'),
('Keyboard', '{"brand": "Logitech", "model": "MX Keys", "features": ["Wireless", "Backlit"]}'),
('Mouse', '{"brand": "Logitech", "model": "MX Master 3", "features": ["Wireless", "Ergonomic"]}');

现在，我们可以使用 JSON_CONTAINS() 来查询包含特定信息的商品。

查询包含 "Dell" 品牌的产品：

SELECT * FROM products WHERE JSON_CONTAINS(details, '{"brand": "Dell"}');

这条语句会返回 Laptop 这条记录，因为它的 details 列包含了 {"brand": "Dell"} 这个 JSON 片段。

查询包含 "Wireless" 特性的产品：

SELECT * FROM products WHERE JSON_CONTAINS(details, '"Wireless"', '$.features');

这条语句会返回 Keyboard 和 Mouse 这两条记录。注意，我们使用了 path 参数 '$.features'，表示只在 features 数组中查找 "Wireless" 字符串。

2. 使用路径进行更精确的查询

path 参数使得 JSON_CONTAINS() 能够进行更精确的查询，只在 JSON 文档的特定部分进行查找。

示例：

假设我们要查询 Laptop 中 CPU 为 "Intel i7" 的产品，我们可以这样做：

SELECT * FROM products WHERE JSON_CONTAINS(details, '"Intel i7"', '$.specs.cpu');

这条语句会返回 Laptop 这条记录，因为 details.specs.cpu 的值为 "Intel i7"。

如果没有指定 path，JSON_CONTAINS() 会在整个 details JSON 文档中查找 "Intel i7"。 如果 details 中其他地方也包含了 "Intel i7"，那么即使不是 CPU 的值，也会被匹配到。 使用 path 可以避免这种情况，确保查询的准确性。

查询拥有特定数组元素的产品

假设 details 中有一个名为 compatible_os 的数组，表示产品兼容的操作系统：

UPDATE products SET details = JSON_SET(details, '$.compatible_os', JSON_ARRAY('Windows', 'macOS', 'Linux')) WHERE name = 'Laptop';
UPDATE products SET details = JSON_SET(details, '$.compatible_os', JSON_ARRAY('Windows', 'macOS')) WHERE name = 'Monitor';

现在，我们可以查询兼容 Linux 的产品：

SELECT * FROM products WHERE JSON_CONTAINS(details, '"Linux"', '$.compatible_os');

这条语句只会返回 Laptop 这条记录。

3. JSON_CONTAINS() 与索引优化

JSON_CONTAINS() 函数可以与 MySQL 的索引结合使用，以提高查询性能。 然而，直接在 JSON 列上创建索引通常不会带来很好的效果。为了优化 JSON_CONTAINS() 的查询，我们需要使用虚拟列 (Virtual Columns) 和函数索引 (Functional Indexes)。

步骤：

1. 创建虚拟列： 为需要经常查询的 JSON 属性创建一个虚拟列。虚拟列的值是根据 JSON 列计算出来的。

   ALTER TABLE products ADD COLUMN cpu VARCHAR(255) AS (JSON_EXTRACT(details, '$.specs.cpu'));

   这里，我们创建了一个名为 cpu 的虚拟列，它的值是 details 列中 specs.cpu 的值。

2. 创建索引： 在虚拟列上创建索引。

   CREATE INDEX idx_cpu ON products (cpu);

现在，我们可以使用虚拟列和索引来优化查询。

优化后的查询：

SELECT * FROM products WHERE cpu = 'Intel i7';

这个查询会使用 idx_cpu 索引，从而大大提高查询速度。

注意事项：

• 虚拟列的类型必须与 JSON 属性的类型匹配。 在上面的例子中，cpu 虚拟列的类型是 VARCHAR(255)，因为 details.specs.cpu 的值是字符串。
• 虚拟列和索引会增加存储空间。
• 当 JSON 列的值发生变化时，虚拟列的值也会自动更新。

表格总结：不同索引策略的性能对比

索引策略	优点	缺点	适用场景
在 JSON 列上直接创建索引	简单易行	效果有限，MySQL 通常无法有效利用 JSON 列上的索引进行范围查询或精确匹配	不推荐使用
虚拟列 + 函数索引	性能提升明显，可以有效利用索引进行查询	增加存储空间，需要维护虚拟列和索引，当 JSON 列的值发生变化时，虚拟列的值也会自动更新，可能会对写入性能产生一定影响	需要频繁查询 JSON 属性的场景
前缀索引 (Prefix Indexing)	可以减小索引的大小，提高索引的效率	需要仔细选择前缀长度，过短的前缀可能导致索引选择性不高，过长的前缀可能增加索引的大小	JSON 属性的值比较长，且只需要匹配部分前缀的场景
全文索引 (Full-Text Indexing)	适用于在 JSON 文本内容中进行全文搜索的场景，例如搜索包含特定关键词的描述信息	只能用于 MATCH AGAINST 语句，不支持精确匹配或范围查询，需要额外的配置和维护	需要进行全文搜索的场景
无索引	简单，无需维护索引	查询性能差，需要全表扫描	数据量较小，查询频率较低的场景

4. JSON_CONTAINS() 的高级用法

除了基本的查询之外，JSON_CONTAINS() 还可以与其他 JSON 函数和 SQL 语句结合使用，实现更复杂的功能。

示例：

查询 features 数组中同时包含 "Wireless" 和 "Ergonomic" 特性的产品：

SELECT *
FROM products
WHERE JSON_CONTAINS(details, JSON_ARRAY('Wireless', 'Ergonomic'), '$.features');

这条语句会返回 Mouse 这条记录，因为它的 features 数组包含了 "Wireless" 和 "Ergonomic" 两个元素。

查询 specs 对象中 ram 大于 "8GB" 的产品：

由于 JSON_EXTRACT() 返回的是字符串，我们需要将其转换为数字才能进行比较。 MySQL 8.0 提供了 JSON_VALUE() 函数，它可以将 JSON 值转换为指定的数据类型。

SELECT *
FROM products
WHERE CAST(JSON_EXTRACT(details, '$.specs.ram') AS UNSIGNED) > 8;

注意： 这个查询是无效的，因为 "16GB" 无法转换为 UNSIGNED。 正确的方式是提取数字部分再进行比较。 更严谨的方式是先提取数字部分，再进行比较。 可以使用正则表达式函数 REGEXP_REPLACE 提取数字：

SELECT *
FROM products
WHERE CAST(REGEXP_REPLACE(JSON_EXTRACT(details, '$.specs.ram'), '[^0-9]+', '') AS UNSIGNED) > 8;

这个查询首先使用 REGEXP_REPLACE 函数从 details.specs.ram 中提取数字部分，然后将其转换为 UNSIGNED 类型，最后与 8 进行比较。

动态构建 target 参数

有时候，我们需要根据变量的值动态构建 JSON_CONTAINS() 函数的 target 参数。 可以使用 CONCAT() 函数来实现。

SET @brand = 'Dell';

SELECT *
FROM products
WHERE JSON_CONTAINS(details, JSON_OBJECT('brand', @brand));

这个查询会返回品牌为 "Dell" 的产品。

5. JSON_CONTAINS() 的性能注意事项

虽然 JSON_CONTAINS() 函数非常强大，但在使用时需要注意性能问题。

• 避免在大型 JSON 文档上使用 JSON_CONTAINS() 进行全表扫描。 这会导致查询速度非常慢。
• 尽量使用路径参数，缩小查询范围。
• 使用虚拟列和索引优化查询。
• 避免在 WHERE 子句中使用复杂的 JSON 函数，这可能会导致索引失效。
• 根据实际情况选择合适的索引策略。

6. 替代方案

虽然 JSON_CONTAINS() 在很多情况下非常有用，但有时可能不是最佳选择。 例如，当需要进行复杂的 JSON 数据查询和分析时，可以考虑使用其他工具和技术，例如：

• NoSQL 数据库： MongoDB、Couchbase 等 NoSQL 数据库专门用于存储和查询 JSON 数据，它们提供了更强大的 JSON 查询功能和更高的性能。
• Elasticsearch： Elasticsearch 是一个强大的搜索和分析引擎，可以用于对 JSON 数据进行全文搜索、聚合和分析。
• 数据仓库： 对于需要进行大规模数据分析的场景，可以使用数据仓库，例如 Snowflake、Amazon Redshift 等。

7. 常见问题和解决方法

• JSON_CONTAINS() 返回 NULL: 如果任何参数为 NULL，JSON_CONTAINS() 将返回 NULL。 需要确保参数不为 NULL，或者使用 IFNULL() 函数处理 NULL 值。
• 查询结果不符合预期: 检查 target 参数是否正确，路径是否正确，以及 JSON 文档的结构是否符合预期。
• 性能问题: 使用虚拟列和索引优化查询，避免在大型 JSON 文档上进行全表扫描。

8. 总结：高效查询 JSON 数据的关键点

JSON_CONTAINS() 函数是 MySQL 中一个强大的 JSON 查询工具。通过合理使用路径参数、虚拟列和索引，可以显著提高 JSON 数据的查询效率。选择合适的索引策略至关重要。