利用 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() 等函数可以有效地处理缺失值和去除引号，提高数据的质量。