如何利用`JSON_EXTRACT()`函数进行嵌套`JSON`查询？

利用 JSON_EXTRACT() 函数进行嵌套 JSON 查询

大家好，今天我们来深入探讨 MySQL 中 JSON_EXTRACT() 函数在处理嵌套 JSON 数据时的应用。JSON_EXTRACT() 是一个强大的工具，它允许我们从 JSON 文档中提取特定路径下的数据，尤其是在处理复杂、嵌套的 JSON 结构时，其作用更加明显。

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

首先，我们回顾一下 JSON_EXTRACT() 的基本语法：

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

• json_doc: 包含 JSON 数据的字符串或列。
• path: 一个或多个 JSON 路径表达式，用于指定要提取的数据的位置。路径表达式以 $ 开头，表示 JSON 文档的根节点。

2. JSON 路径表达式的构成

JSON 路径表达式是 JSON_EXTRACT() 的核心，它决定了我们如何精确地定位到 JSON 文档中的目标数据。路径表达式可以包含以下元素：

• $: 表示根节点。
• .key: 用于访问 JSON 对象中的键值对。
• [index]: 用于访问 JSON 数组中的元素，索引从 0 开始。
• [*]: 用于访问 JSON 数组中的所有元素。
• **: 用于递归搜索，匹配所有层级的元素。这个用法需要 MySQL 8.0.17 及以上版本。

3. 嵌套 JSON 数据的例子

为了更好地理解 JSON_EXTRACT() 在嵌套 JSON 查询中的应用，我们先创建一个包含嵌套 JSON 数据的表。

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_details JSON
);

INSERT INTO orders (order_details) VALUES
(
    '
    {
        "customer": {
            "name": "Alice Smith",
            "email": "[email protected]",
            "address": {
                "street": "123 Main St",
                "city": "Anytown",
                "zipcode": "12345"
            }
        },
        "items": [
            {
                "product_id": 101,
                "product_name": "Laptop",
                "quantity": 1,
                "price": 1200.00
            },
            {
                "product_id": 102,
                "product_name": "Mouse",
                "quantity": 1,
                "price": 25.00
            }
        ],
        "order_date": "2023-10-27",
        "total_amount": 1225.00
    }
    '
),
(
    '
    {
        "customer": {
            "name": "Bob Johnson",
            "email": "[email protected]",
            "address": {
                "street": "456 Oak Ave",
                "city": "Somewhereville",
                "zipcode": "67890"
            }
        },
        "items": [
            {
                "product_id": 201,
                "product_name": "Keyboard",
                "quantity": 1,
                "price": 75.00
            },
            {
                "product_id": 202,
                "product_name": "Monitor",
                "quantity": 1,
                "price": 300.00
            }
        ],
        "order_date": "2023-10-28",
        "total_amount": 375.00
    }
    '
);

现在，我们有了 orders 表，其中 order_details 列存储了包含客户信息、商品信息和订单信息的 JSON 数据。

4. 提取嵌套对象中的数据

假设我们需要提取每个订单中客户的姓名和城市。我们可以使用以下查询：

SELECT
    JSON_EXTRACT(order_details, '$.customer.name') AS customer_name,
    JSON_EXTRACT(order_details, '$.customer.address.city') AS customer_city
FROM
    orders;

这个查询使用了 JSON_EXTRACT() 函数，并通过路径表达式 $.customer.name 和 $.customer.address.city 分别提取了客户姓名和城市。

customer_name	customer_city
"Alice Smith"	"Anytown"
"Bob Johnson"	"Somewhereville"

5. 提取数组中的数据

如果我们需要提取每个订单中第一个商品的名称，可以使用以下查询：

SELECT
    JSON_EXTRACT(order_details, '$.items[0].product_name') AS first_product_name
FROM
    orders;

在这个查询中，$.items[0].product_name 路径表达式首先访问了 items 数组，然后使用 [0] 获取了数组中的第一个元素，最后提取了 product_name 属性。

first_product_name
"Laptop"
"Keyboard"

6. 提取数组中的所有元素

如果我们想提取每个订单中所有商品的名称，可以使用 [*] 通配符：

SELECT
    JSON_EXTRACT(order_details, '$.items[*].product_name') AS all_product_names
FROM
    orders;

这个查询会返回一个 JSON 数组，其中包含了所有商品的名称。

all_product_names
["Laptop", "Mouse"]
["Keyboard", "Monitor"]

如果我们想将数组展开成多行，可以使用 JSON_TABLE() 函数 (MySQL 8.0 及以上版本)：

SELECT
    ot.product_name
FROM
    orders
    CROSS JOIN JSON_TABLE(
        order_details,
        '$.items[*]'
        COLUMNS (
            product_name VARCHAR(255) PATH '$.product_name'
        )
    ) AS ot;

这个查询首先使用 JSON_TABLE() 函数将 items 数组展开成一个临时表，然后从这个临时表中提取 product_name 属性。

product_name
"Laptop"
"Mouse"
"Keyboard"
"Monitor"

7. 使用条件进行过滤

JSON_EXTRACT() 可以与其他 SQL 语句结合使用，例如 WHERE 子句，根据 JSON 数据中的值进行过滤。

例如，要查找所有总金额大于 1000 的订单，可以使用以下查询：

SELECT
    id
FROM
    orders
WHERE
    JSON_EXTRACT(order_details, '$.total_amount') > 1000;

这个查询首先使用 JSON_EXTRACT() 提取 total_amount，然后使用 WHERE 子句过滤结果。

id
1

要查找所有购买了 "Laptop" 的订单，可以使用 JSON_CONTAINS() 函数：

SELECT id
FROM orders
WHERE JSON_CONTAINS(order_details, '{"product_name": "Laptop"}', '$.items');

更精确的查找需要配合JSON_SEARCH()函数, 查找items数组中product_name为"Laptop"的订单

SELECT id
FROM orders
WHERE JSON_SEARCH(order_details, 'one', 'Laptop', NULL, '$.items[*].product_name') IS NOT NULL;

8. 使用 `` 进行递归搜索 (MySQL 8.0.17 及以上)**

** 路径表达式允许我们递归地搜索 JSON 文档中的所有层级。 例如，如果我们想找到 JSON 文档中所有包含 "city" 键的路径，可以使用以下查询：

假设我们的 orders 表中新增了更深层嵌套的数据：

INSERT INTO orders (order_details) VALUES (
    '{
        "company": {
            "location": {
                "hq": {
                    "address": {
                        "city": "New York"
                    }
                },
                "regional_offices": [
                    {
                        "address": {
                            "city": "Los Angeles"
                        }
                    },
                    {
                        "address": {
                            "city": "Chicago"
                        }
                    }
                ]
            }
        }
    }'
);

现在，我们可以使用 ** 递归搜索 city：

SELECT JSON_EXTRACT(order_details, '$**.city') FROM orders WHERE id = 3;

这个查询会返回一个 JSON 数组，其中包含了所有找到的 city 的值。

JSON_EXTRACT(order_details, ‘$**.city’)
["New York", "Los Angeles", "Chicago"]

9. JSON_UNQUOTE() 用于去除引号

JSON_EXTRACT()返回的结果通常带有引号，如果需要去除引号，可以使用JSON_UNQUOTE()函数。

SELECT
    JSON_UNQUOTE(JSON_EXTRACT(order_details, '$.customer.name')) AS customer_name,
    JSON_UNQUOTE(JSON_EXTRACT(order_details, '$.customer.address.city')) AS customer_city
FROM
    orders;

customer_name	customer_city
Alice Smith	Anytown
Bob Johnson	Somewhereville

10. 错误处理

当路径表达式无效或者 JSON 文档不符合预期结构时，JSON_EXTRACT() 函数可能会返回 NULL。 为了避免这种情况，可以使用 IFNULL() 或 COALESCE() 函数来提供默认值。

例如，如果 customer.address.city 可能不存在，可以使用以下查询：

SELECT
    customer_name,
    IFNULL(JSON_UNQUOTE(JSON_EXTRACT(order_details, '$.customer.address.city')), 'Unknown') AS customer_city
FROM
    (SELECT JSON_UNQUOTE(JSON_EXTRACT(order_details, '$.customer.name')) AS customer_name, order_details FROM orders) AS subquery;

如果 customer.address.city 不存在，则返回 "Unknown"。

总结：JSON_EXTRACT() 的强大之处

JSON_EXTRACT() 函数在处理嵌套 JSON 数据时非常强大，它允许我们通过路径表达式精确地定位和提取所需的数据。结合 JSON_TABLE()、JSON_CONTAINS()、JSON_SEARCH() 和 JSON_UNQUOTE() 等函数，我们可以更加灵活地处理 JSON 数据，并将其集成到 SQL 查询中。

总结：灵活运用路径表达式是关键

掌握 JSON 路径表达式的语法是使用 JSON_EXTRACT() 的关键。 通过合理地构建路径表达式，我们可以轻松地从复杂的 JSON 文档中提取所需的数据，并与其他 SQL 功能结合使用，实现更高级的查询和分析。

总结：错误处理和数据清洗很重要

在使用 JSON_EXTRACT() 时，需要注意错误处理和数据清洗，确保查询的稳定性和准确性。 使用 IFNULL()、COALESCE() 和 JSON_UNQUOTE() 等函数可以有效地处理缺失值和去除引号，提高数据的质量。