JSON_EXTRACT 在 MySQL 中的应用：快速提取 JSON 数据 – wiki基地

作者： /

JSON_EXTRACT 在 MySQL 中的应用：快速提取 JSON 数据

随着互联网的快速发展，JSON（JavaScript Object Notation）作为一种轻量级的数据交换格式，被广泛应用于 Web 应用、移动应用以及后端服务之间的数据传输和存储。MySQL 5.7 版本之后开始原生支持 JSON 数据类型，并提供了强大的 JSON 函数，使得在数据库中存储和操作 JSON 数据变得更加方便快捷。其中，JSON_EXTRACT 函数作为最核心的 JSON 函数之一，能够高效地从 JSON 文档中提取指定路径的数据，极大地提升了数据处理的效率和灵活性。本文将深入探讨 JSON_EXTRACT 函数在 MySQL 中的应用，包括其语法、用法、性能优化以及实际应用场景，帮助读者全面掌握这一强大工具。

一、JSON 数据类型和 JSON 函数概述

在深入了解 JSON_EXTRACT 之前，我们需要先对 MySQL 中的 JSON 数据类型和相关的 JSON 函数有一个整体的认识。

  • JSON 数据类型: MySQL 的 JSON 数据类型专门用于存储 JSON 格式的数据。 相比于将 JSON 数据存储为 VARCHAR 或 TEXT 类型，JSON 数据类型具有以下优势：

    • 验证性: 插入 JSON 数据时，MySQL 会自动验证其格式是否符合 JSON 规范，确保数据的有效性。
    • 索引优化: 可以对 JSON 字段的特定元素建立索引，提高查询效率。
    • 存储效率: JSON 数据类型采用二进制格式存储，相比于文本格式，可以节省存储空间。

  • 常用 JSON 函数: MySQL 提供了丰富的 JSON 函数，用于创建、修改、查询和操作 JSON 数据。 以下是一些常用的 JSON 函数：

    • JSON_OBJECT()：用于创建 JSON 对象。
    • JSON_ARRAY()：用于创建 JSON 数组。
    • JSON_MERGE_PATCH() / JSON_MERGE_PRESERVE()：用于合并 JSON 对象。
    • JSON_SET()：用于设置 JSON 文档中指定路径的值。
    • JSON_REPLACE()：用于替换 JSON 文档中指定路径的值。
    • JSON_INSERT()：用于在 JSON 文档中指定路径插入新的键值对。
    • JSON_REMOVE()：用于从 JSON 文档中删除指定路径的键值对。
    • JSON_CONTAINS()：用于判断 JSON 文档是否包含指定的键值对或子文档。
    • JSON_CONTAINS_PATH()：用于判断 JSON 文档是否包含指定的路径。
    • JSON_KEYS()：用于提取 JSON 对象的键。
    • JSON_LENGTH()：用于获取 JSON 数组或对象的长度。
    • JSON_VALID()：用于验证 JSON 数据的有效性。
    • JSON_EXTRACT()：用于从 JSON 文档中提取指定路径的数据。 这是本文的重点。

二、JSON_EXTRACT 函数的语法和用法

JSON_EXTRACT 函数的主要作用是从 JSON 文档中提取指定路径的值。其基本语法如下：

sql
JSON_EXTRACT(json_doc, path1[, path2, ...])

  • json_doc: 需要提取数据的 JSON 文档，可以是 JSON 类型的列、JSON 字符串或 JSON 变量。
  • path1, path2, ...: JSON 路径，用于指定要提取数据的路径。 可以指定一个或多个路径。 每个路径都以 $ 符号开头，表示根节点。

JSON 路径的语法规则：

  • $.key: 表示提取根节点下的 key 对应的值。
  • $."key with space": 如果键名包含空格或特殊字符，需要使用双引号括起来。
  • $[index]: 表示提取数组中索引为 index 的元素。 索引从 0 开始。
  • $.key[index]: 表示提取根节点下的 key 对应的数组中索引为 index 的元素。
  • $[start, end]: (MySQL 8.0.17+) 用于提取数组的一部分，从 start (包含) 到 end (包含)。 例如 $[1, 3] 提取索引为 1, 2, 3 的元素。
  • $.*: (MySQL 8.0.17+) 用于提取对象中的所有值。
  • $[*]: (MySQL 8.0.17+) 用于提取数组中的所有元素。
  • **: (MySQL 8.0.17+) 递归地查找所有匹配的路径。 例如， $.**.city 会查找所有层级嵌套的 JSON 对象中名为 city 的键对应的值。

JSON_EXTRACT 函数的返回值：

  • 如果路径存在，并且能够找到对应的值，则返回该值。
  • 如果路径不存在，或者 JSON 文档不是有效的 JSON 格式，则返回 NULL。
  • 如果指定了多个路径，则返回一个包含所有提取值的 JSON 数组。

示例：

假设我们有一个名为 products 的表，其中包含一个名为 details 的 JSON 列，存储了产品的详细信息。 details 列的数据示例如下：

json
{
"product_id": 101,
"product_name": "Laptop",
"price": 1200.00,
"category": "Electronics",
"attributes": {
"screen_size": "15.6 inch",
"processor": "Intel Core i7",
"memory": "16GB",
"storage": "512GB SSD"
},
"tags": ["new", "featured", "discount"]
}

以下是一些使用 JSON_EXTRACT 函数的示例：

“`sql
— 提取 product_name
SELECT JSON_EXTRACT(details, ‘$.product_name’) AS product_name FROM products WHERE product_id = 101;
— 返回： “Laptop”

— 提取 price
SELECT JSON_EXTRACT(details, ‘$.price’) AS price FROM products WHERE product_id = 101;
— 返回： 1200.00

— 提取 attributes 中的 screen_size
SELECT JSON_EXTRACT(details, ‘$.attributes.screen_size’) AS screen_size FROM products WHERE product_id = 101;
— 返回： “15.6 inch”

— 提取 tags 数组中的第一个元素
SELECT JSON_EXTRACT(details, ‘$.tags[0]’) AS first_tag FROM products WHERE product_id = 101;
— 返回： “new”

— 提取 tags 数组中的所有元素 (MySQL 8.0.17+)
SELECT JSON_EXTRACT(details, ‘$.tags[*]’) AS all_tags FROM products WHERE product_id = 101;
— 返回： [“new”, “featured”, “discount”]

— 提取 product_name 和 price (返回 JSON 数组)
SELECT JSON_EXTRACT(details, ‘$.product_name’, ‘$.price’) AS product_info FROM products WHERE product_id = 101;
— 返回： [“Laptop”, 1200.00]
“`

三、JSON_EXTRACT 函数的高级用法

除了基本的提取功能之外，JSON_EXTRACT 函数还可以与其他 SQL 函数结合使用，实现更复杂的数据处理和分析。

  • 与 JSON_UNQUOTE 函数结合使用: JSON_EXTRACT 函数提取出的字符串值通常带有双引号。 JSON_UNQUOTE 函数可以移除这些双引号，返回原始的字符串值。

sql
SELECT JSON_UNQUOTE(JSON_EXTRACT(details, '$.product_name')) AS product_name FROM products WHERE product_id = 101;
-- 返回： Laptop (不带双引号)

  • 与 AS 别名结合使用: 使用 AS 关键字可以为提取出的字段指定别名，提高查询结果的可读性。 前面的示例中已经使用了 AS 别名。

  • 与 WHERE 子句结合使用: 可以将 JSON_EXTRACT 函数用于 WHERE 子句中，根据 JSON 文档中的特定值进行过滤。

sql
SELECT * FROM products WHERE JSON_EXTRACT(details, '$.category') = '"Electronics"';
-- 返回 category 为 "Electronics" 的所有产品。 注意: 需要加上双引号进行比较。

更好的方式是使用 JSON_CONTAINS 函数：

sql
SELECT * FROM products WHERE JSON_CONTAINS(details, '{"category": "Electronics"}');
-- 返回 category 为 "Electronics" 的所有产品。

  • 与聚合函数结合使用: 可以将 JSON_EXTRACT 函数与聚合函数（如 SUM(), AVG(), MAX(), MIN(), COUNT()）结合使用，对 JSON 数据进行统计分析。

sql
SELECT AVG(JSON_EXTRACT(details, '$.price')) AS average_price FROM products WHERE JSON_EXTRACT(details, '$.category') = '"Electronics"';
-- 返回 Electronics 类别产品的平均价格。

四、JSON_EXTRACT 函数的性能优化

虽然 JSON_EXTRACT 函数功能强大，但在处理大量数据时，可能会遇到性能瓶颈。 以下是一些性能优化的建议：

  • 使用索引: 如果经常需要根据 JSON 文档中的特定值进行查询，可以考虑在该字段上建立索引。 MySQL 支持对 JSON 字段的虚拟列建立索引。 例如，要对 details 列中的 product_name 建立索引，可以执行以下操作：

sql
ALTER TABLE products ADD COLUMN product_name VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(details, '$.product_name'))) VIRTUAL;
CREATE INDEX idx_product_name ON products (product_name);

  • 避免在 WHERE 子句中使用 LIKE 操作符: 尽量避免在 WHERE 子句中使用 LIKE 操作符对 JSON 文档进行模糊匹配，因为这会导致全表扫描，影响性能。 可以使用 JSON_CONTAINS 函数或其他更精确的匹配方式。

  • 减少 JSON_EXTRACT 函数的调用次数: 尽量在一次查询中提取所有需要的数据，避免多次调用 JSON_EXTRACT 函数。

  • 使用 JSON_TABLE 函数 (MySQL 8.0+): 对于复杂的数据提取和转换，可以使用 JSON_TABLE 函数将 JSON 数据转换为关系型数据，然后进行后续处理。 JSON_TABLE 通常比多次调用 JSON_EXTRACT 效率更高。

  • 优化 JSON 文档的结构: 尽量保持 JSON 文档的结构简单清晰，避免过多的嵌套和冗余数据，这可以提高 JSON_EXTRACT 函数的效率。

  • 硬件升级: 如果数据量非常大，并且查询频率很高，可以考虑升级服务器的硬件配置，如 CPU、内存和磁盘，以提高整体性能。

五、JSON_EXTRACT 函数的实际应用场景

JSON_EXTRACT 函数在实际应用中有着广泛的应用场景，以下是一些常见的例子：

  • 日志分析: 可以将应用程序的日志信息存储为 JSON 格式，然后使用 JSON_EXTRACT 函数提取关键信息，如错误代码、用户 ID、请求时间等，进行分析和监控。

  • 电商平台: 可以将商品的详细信息存储为 JSON 格式，包括商品名称、价格、描述、属性等，然后使用 JSON_EXTRACT 函数提取特定信息，用于商品展示、搜索和推荐。

  • 社交网络: 可以将用户的个人资料存储为 JSON 格式，包括姓名、年龄、性别、兴趣爱好等，然后使用 JSON_EXTRACT 函数提取特定信息，用于用户画像分析和社交推荐。

  • 物联网 (IoT): 可以将传感器采集的数据存储为 JSON 格式，包括温度、湿度、压力等，然后使用 JSON_EXTRACT 函数提取特定信息，用于实时监控和预警。

  • 配置管理: 可以将应用程序的配置信息存储为 JSON 格式，包括数据库连接信息、API 密钥、缓存配置等，然后使用 JSON_EXTRACT 函数提取特定信息，用于动态配置和管理。

六、JSON_EXTRACT 与其他 JSON 函数的比较

虽然 JSON_EXTRACT 函数非常强大，但在某些情况下，使用其他 JSON 函数可能更合适。 以下是一些比较：

  • JSON_EXTRACT vs JSON_VALUE (MySQL 8.0.21+): JSON_VALUE 函数类似于 JSON_EXTRACT，但它返回的是 SQL 数据类型，而不是 JSON 数据类型。 如果只需要提取一个简单的标量值，并且需要进行类型转换，可以使用 JSON_VALUE。

“`sql
— 使用 JSON_EXTRACT
SELECT JSON_EXTRACT(details, ‘$.price’) AS price FROM products WHERE product_id = 101;
— 返回： 1200.00 (JSON 数据类型)

— 使用 JSON_VALUE
SELECT JSON_VALUE(details, ‘$.price’ RETURNING DECIMAL(10, 2)) AS price FROM products WHERE product_id = 101;
— 返回： 1200.00 (DECIMAL 数据类型)
“`

  • JSON_EXTRACT vs JSON_CONTAINS: JSON_EXTRACT 用于提取指定路径的值，而 JSON_CONTAINS 用于判断 JSON 文档是否包含指定的键值对或子文档。 如果只需要判断是否存在，可以使用 JSON_CONTAINS，它通常比 JSON_EXTRACT 更高效。

  • JSON_EXTRACT vs JSON_KEYS: JSON_EXTRACT 用于提取指定路径的值，而 JSON_KEYS 用于提取 JSON 对象的键。 如果只需要获取键的列表，可以使用 JSON_KEYS。

七、总结

JSON_EXTRACT 函数是 MySQL 中处理 JSON 数据的重要工具之一。 通过灵活运用 JSON_EXTRACT 函数，可以高效地从 JSON 文档中提取所需的数据，并与其他 SQL 函数结合使用，实现更复杂的数据处理和分析。 然而，在使用 JSON_EXTRACT 函数时，需要注意性能优化，例如使用索引、避免 LIKE 操作符、减少函数调用次数等，以确保查询效率。 掌握 JSON_EXTRACT 函数的语法、用法、性能优化以及实际应用场景，将极大地提升你在 MySQL 中处理 JSON 数据的能力。

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部