MySQL的`JSON_EXTRACT()`：如何从JSON数据中提取特定字段？ - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL JSON_EXTRACT()：JSON 数据提取的利器

大家好！今天我们来深入探讨 MySQL 中一个非常强大的函数：JSON_EXTRACT()。在现代应用程序开发中，JSON (JavaScript Object Notation) 已经成为一种非常流行的数据格式，用于存储和交换数据。MySQL 从 5.7 版本开始原生支持 JSON 数据类型，并提供了一系列函数来操作 JSON 数据，JSON_EXTRACT() 就是其中最重要的一个。

本次讲座将涵盖以下几个方面：

JSON_EXTRACT() 的基本语法和用法
如何使用路径表达式 (path expressions) 精确定位 JSON 数据中的字段
提取数组和嵌套 JSON 对象中的数据
处理 JSON_EXTRACT() 返回的 NULL 值
JSON_EXTRACT() 的性能考量和优化建议
高级用法：结合 JSON_CONTAINS() 和 JSON_SEARCH()
实际案例分析：电商平台订单数据处理

1. `JSON_EXTRACT()` 的基本语法和用法

JSON_EXTRACT() 函数用于从 JSON 文档中提取指定路径的数据。其基本语法如下：

JSON_EXTRACT(json_doc, path[, path] ...)

json_doc：包含 JSON 数据的列或 JSON 格式的字符串。
path：一个字符串，表示要提取的数据的路径表达式。可以指定多个路径，用逗号分隔。

JSON_EXTRACT() 返回与指定路径匹配的 JSON 值。如果任何路径无效或不存在，则返回 NULL。

示例 1：提取简单的 JSON 对象中的字段

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

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

INSERT INTO products (id, name, details) VALUES
(1, 'Laptop', '{"brand": "Dell", "model": "XPS 13", "price": 1200, "features": ["Intel i7", "16GB RAM", "512GB SSD"]}');

INSERT INTO products (id, name, details) VALUES
(2, 'Smartphone', '{"brand": "Samsung", "model": "Galaxy S23", "price": 900, "features": ["Snapdragon 8 Gen 2", "8GB RAM", "128GB Storage"]}');

SELECT * FROM products;

现在，我们可以使用 JSON_EXTRACT() 提取产品的品牌和价格：

SELECT
    id,
    name,
    JSON_EXTRACT(details, '$.brand') AS brand,
    JSON_EXTRACT(details, '$.price') AS price
FROM
    products;

上述查询将返回以下结果：

id	name	brand	price
1	Laptop	"Dell"	1200
2	Smartphone	"Samsung"	900

注意：JSON_EXTRACT() 返回的实际上仍然是 JSON 字符串。如果需要将提取的值转换为特定的数据类型，可以使用 CAST() 函数。例如：

SELECT
    id,
    name,
    JSON_EXTRACT(details, '$.brand') AS brand,
    CAST(JSON_EXTRACT(details, '$.price') AS UNSIGNED) AS price
FROM
    products;

这样，price 列将返回一个无符号整数。

2. 使用路径表达式 (path expressions) 精确定位 JSON 数据中的字段

路径表达式是 JSON_EXTRACT() 函数中用于指定要提取的数据位置的关键。路径表达式以 $ 开头，表示 JSON 文档的根。

$.key：提取 JSON 对象中键为 key 的值。
$[index]：提取 JSON 数组中索引为 index 的元素（索引从 0 开始）。
$."key with space"：如果键名包含空格或其他特殊字符，可以使用双引号将其括起来。
$[*]：提取 JSON 数组中的所有元素。
$**.key：递归地查找 JSON 文档中所有键为 key 的值。 (注意： MySQL 8.0.17 及更高版本支持双星号)

示例 2：提取数组中的元素

继续使用 products 表，我们可以提取产品的第一个特性：

SELECT
    id,
    name,
    JSON_EXTRACT(details, '$.features[0]') AS first_feature
FROM
    products;

结果：

id	name	first_feature
1	Laptop	"Intel i7"
2	Smartphone	"Snapdragon 8 Gen 2"

示例 3：使用 JSON_EXTRACT 提取多个字段

一次性提取多个字段：

SELECT
    id,
    name,
    JSON_EXTRACT(details, '$.brand', '$.price', '$.features[0]')
FROM
    products;

这个查询会返回一个包含提取值的 JSON 数组。

3. 提取数组和嵌套 JSON 对象中的数据

JSON 数据可以包含嵌套的对象和数组。JSON_EXTRACT() 可以通过组合路径表达式来提取这些嵌套的数据。

示例 4：提取嵌套对象中的字段

假设 products 表的 details 列包含以下 JSON 数据：

{
    "brand": "Dell",
    "model": "XPS 13",
    "price": 1200,
    "specifications": {
        "cpu": "Intel i7-1260P",
        "ram": "16GB",
        "storage": "512GB SSD"
    }
}

要提取 CPU 型号，可以使用以下查询：

SELECT
    id,
    name,
    JSON_EXTRACT(details, '$.specifications.cpu') AS cpu
FROM
    products;

示例 5：提取嵌套数组中的特定元素

假设 products 表的 details 列包含以下 JSON 数据：

{
    "brand": "Dell",
    "model": "XPS 13",
    "price": 1200,
    "reviews": [
        {"author": "Alice", "rating": 5},
        {"author": "Bob", "rating": 4},
        {"author": "Charlie", "rating": 5}
    ]
}

要提取第一个评论的作者姓名，可以使用以下查询：

SELECT
    id,
    name,
    JSON_EXTRACT(details, '$.reviews[0].author') AS first_review_author
FROM
    products;

4. 处理 `JSON_EXTRACT()` 返回的 `NULL` 值

当指定的路径在 JSON 文档中不存在时，JSON_EXTRACT() 会返回 NULL。在实际应用中，我们需要妥善处理这些 NULL 值，以避免程序出错或产生不符合预期的结果。

示例 6：使用 COALESCE() 函数处理 NULL 值

COALESCE() 函数返回其参数列表中第一个非 NULL 的表达式。我们可以使用它来为 JSON_EXTRACT() 返回的 NULL 值提供一个默认值：

SELECT
    id,
    name,
    COALESCE(JSON_EXTRACT(details, '$.discount'), '0') AS discount
FROM
    products;

如果 details 列中没有 discount 字段，则 JSON_EXTRACT() 将返回 NULL，COALESCE() 函数将返回 '0'。

示例 7：使用 IFNULL() 函数处理 NULL 值

IFNULL() 函数类似于 COALESCE()，但只接受两个参数。

SELECT
    id,
    name,
    IFNULL(JSON_EXTRACT(details, '$.discount'), '0') AS discount
FROM
    products;

示例 8：使用 CASE 语句处理 NULL 值

CASE 语句提供了更灵活的 NULL 值处理方式。

SELECT
    id,
    name,
    CASE
        WHEN JSON_EXTRACT(details, '$.discount') IS NULL THEN '0'
        ELSE JSON_EXTRACT(details, '$.discount')
    END AS discount
FROM
    products;

5. `JSON_EXTRACT()` 的性能考量和优化建议

虽然 JSON_EXTRACT() 非常方便，但在处理大量 JSON 数据时，性能可能会成为一个瓶颈。以下是一些优化建议：

尽量避免在 WHERE 子句中使用 JSON_EXTRACT()。 如果需要在 WHERE 子句中使用 JSON 数据进行过滤，可以考虑创建虚拟列 (virtual columns) 或生成列 (generated columns)，并对这些列创建索引。
```
ALTER TABLE products ADD COLUMN brand VARCHAR(255) GENERATED ALWAYS AS (JSON_EXTRACT(details, '$.brand'));
CREATE INDEX idx_brand ON products (brand);

SELECT * FROM products WHERE brand = 'Dell';
```
只提取需要的字段。 避免提取整个 JSON 文档，只提取应用程序实际需要的字段。
使用合适的 JSON 数据结构。 JSON 数据的结构会影响 JSON_EXTRACT() 的性能。选择合适的结构可以简化路径表达式，提高查询效率。
考虑使用 JSON 索引 (MySQL 8.0 及更高版本)。 JSON 索引可以显著提高包含 JSON 列的查询性能。
```
ALTER TABLE products ADD INDEX idx_details ((CAST(JSON_EXTRACT(details, '$.price') AS UNSIGNED)));
```
分析查询执行计划。 使用 EXPLAIN 语句分析查询执行计划，可以帮助你识别性能瓶颈并进行优化。

6. 高级用法：结合 `JSON_CONTAINS()` 和 `JSON_SEARCH()`

JSON_EXTRACT() 可以与其他 JSON 函数结合使用，以实现更复杂的查询。

JSON_CONTAINS()： 用于检查 JSON 文档是否包含指定的 JSON 值。
```
SELECT * FROM products WHERE JSON_CONTAINS(details, '{"brand": "Dell"}');
```
JSON_SEARCH()： 用于在 JSON 文档中查找指定的字符串，并返回匹配的路径。
```
SELECT id, name FROM products WHERE JSON_SEARCH(details, 'one', 'Dell') IS NOT NULL;
```

结合使用这些函数，可以实现更强大的 JSON 数据查询和分析功能。例如，可以先使用 JSON_SEARCH() 找到包含特定值的路径，然后使用 JSON_EXTRACT() 提取该路径对应的值。

7. 实际案例分析：电商平台订单数据处理

让我们来看一个实际的案例：电商平台订单数据处理。假设我们有一个名为 orders 的表，其中包含一个名为 order_details 的 JSON 列，存储了订单的详细信息：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATETIME,
    order_details JSON
);

INSERT INTO orders (order_id, customer_id, order_date, order_details) VALUES
(1, 101, '2023-10-26 10:00:00', '{"items": [{"product_id": 1, "quantity": 2, "price": 1200}, {"product_id": 2, "quantity": 1, "price": 900}], "shipping_address": {"street": "123 Main St", "city": "Anytown", "state": "CA", "zip": "91234"}}');

INSERT INTO orders (order_id, customer_id, order_date, order_details) VALUES
(2, 102, '2023-10-26 11:00:00', '{"items": [{"product_id": 3, "quantity": 3, "price": 500}, {"product_id": 4, "quantity": 2, "price": 300}], "shipping_address": {"street": "456 Oak Ave", "city": "Springfield", "state": "IL", "zip": "62704"}}');

需求 1：提取订单的总金额

我们需要遍历 items 数组，计算每个商品的金额（数量 * 价格），然后将所有商品的金额加起来。

SELECT
    order_id,
    (
        SELECT SUM(item_total)
        FROM JSON_TABLE(
            order_details,
            '$.items[*]' COLUMNS (
                product_id INT PATH '$.product_id',
                quantity INT PATH '$.quantity',
                price DECIMAL(10, 2) PATH '$.price',
                item_total DECIMAL(10, 2) AS (quantity * price)
            )
        ) AS item_totals
    ) AS total_amount
FROM
    orders;

这个查询使用了 JSON_TABLE() 函数将 items 数组转换为一个虚拟表，然后使用 SUM() 函数计算总金额。

需求 2：提取订单的收货地址

SELECT
    order_id,
    JSON_EXTRACT(order_details, '$.shipping_address.street') AS street,
    JSON_EXTRACT(order_details, '$.shipping_address.city') AS city,
    JSON_EXTRACT(order_details, '$.shipping_address.state') AS state,
    JSON_EXTRACT(order_details, '$.shipping_address.zip') AS zip
FROM
    orders;

需求 3：查找包含特定商品的订单

假设我们需要查找包含 product_id 为 1 的订单。

SELECT order_id
FROM orders
WHERE JSON_CONTAINS(order_details, CAST('{"items": [{"product_id": 1}]}' AS JSON));

或者，使用 JSON_SEARCH：

SELECT order_id
FROM orders
WHERE JSON_SEARCH(order_details, 'one', '1', NULL, '$.items[*].product_id') IS NOT NULL;

这些案例展示了 JSON_EXTRACT() 在实际应用中的强大功能。通过灵活运用 JSON_EXTRACT() 和其他 JSON 函数，我们可以轻松地处理复杂的 JSON 数据。

总结: 灵活提取JSON数据

总而言之，JSON_EXTRACT() 是 MySQL 中处理 JSON 数据的关键工具。掌握其语法、路径表达式以及与其他 JSON 函数的结合使用，可以让你在处理 JSON 数据时更加高效和灵活。记住，性能优化同样重要，特别是在处理大型 JSON 数据集时。

MySQL JSON_EXTRACT()：JSON 数据提取的利器

1. JSON_EXTRACT() 的基本语法和用法

2. 使用路径表达式 (path expressions) 精确定位 JSON 数据中的字段

3. 提取数组和嵌套 JSON 对象中的数据

4. 处理 JSON_EXTRACT() 返回的 NULL 值

5. JSON_EXTRACT() 的性能考量和优化建议

6. 高级用法：结合 JSON_CONTAINS() 和 JSON_SEARCH()