MySQL高级函数之：`JSON_EXTRACT()`：其在提取`JSON`字段时的路径表达式。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL `JSON_EXTRACT()` 函数详解：路径表达式的艺术

大家好，今天我们深入探讨 MySQL 中处理 JSON 数据的一个核心函数：JSON_EXTRACT()。理解并熟练运用 JSON_EXTRACT() 及其路径表达式，对于从 JSON 字符串中高效提取所需数据至关重要。本次讲座将围绕路径表达式的语法、应用场景、以及一些高级用法展开，希望能帮助大家更好地掌握这一强大的工具。

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

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

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

json_doc: 包含 JSON 数据的字符串。可以是列名、变量或直接的 JSON 字符串。
path: 一个或多个路径表达式，用于指定要提取的 JSON 文档中的位置。
可以提取多个路径的数据。

该函数返回一个包含所有匹配值的 JSON 数组。如果没有匹配的值，则返回 NULL。

2. 路径表达式：深入解析

路径表达式是 JSON_EXTRACT() 函数的核心，它定义了如何在 JSON 文档中导航并定位到特定数据。MySQL 使用一种类似 XPath 的语法来表示路径。

2.1 基本路径表达式

$.: 表示根对象。所有路径表达式都以 $. 开始。
.key: 访问 JSON 对象中的键为 key 的属性。
[index]: 访问 JSON 数组中索引为 index 的元素（索引从 0 开始）。

示例：

假设我们有以下 JSON 文档存储在名为 data 的列中：

{
  "name": "Alice",
  "age": 30,
  "address": {
    "street": "123 Main St",
    "city": "Anytown"
  },
  "hobbies": ["reading", "hiking", "coding"]
}

以下是一些使用基本路径表达式的示例：

SQL 语句	返回值	说明
`SELECT JSON_EXTRACT(data, '$.name');`	`"Alice"`	提取 `name` 属性的值。
`SELECT JSON_EXTRACT(data, '$.age');`	`30`	提取 `age` 属性的值。
`SELECT JSON_EXTRACT(data, '$.address.city');`	`"Anytown"`	提取嵌套对象 `address` 中 `city` 属性的值。
`SELECT JSON_EXTRACT(data, '$.hobbies[0]');`	`"reading"`	提取 `hobbies` 数组中第一个元素的值。
`SELECT JSON_EXTRACT(data, '$.hobbies[2]');`	`"coding"`	提取 `hobbies` 数组中第三个元素的值。

*2.2 通配符：`和`

MySQL 提供了两个通配符来增强路径表达式的灵活性：

*``**: 匹配对象或数组中的所有成员。
``**: 递归地匹配对象中的所有成员。

示例：

假设我们有以下 JSON 文档：

{
  "employees": [
    {
      "name": "Bob",
      "department": "Sales"
    },
    {
      "name": "Charlie",
      "department": "Marketing"
    },
    {
      "name": "David",
      "department": "Sales"
    }
  ]
}

以下是一些使用通配符的示例：

SQL 语句	返回值	说明
`SELECT JSON_EXTRACT(data, '$.employees[*].name');`	`["Bob", "Charlie", "David"]`	提取 `employees` 数组中所有对象的 `name` 属性的值。
`SELECT JSON_EXTRACT(data, '$.employees[*].department');`	`["Sales", "Marketing", "Sales"]`	提取 `employees` 数组中所有对象的 `department` 属性的值。
`SELECT JSON_EXTRACT(data, '$**.name');`	`["Bob", "Charlie", "David"]` (假设 JSON 结构只有这一层 employees 数组包含 name 属性)	在整个 JSON 文档中递归查找名为 `name` 的属性，并返回其值。注意：如果 JSON 文档中存在多个名为 `name` 的属性，此查询将返回所有匹配的值。

`` 通配符的更复杂示例：**

假设我们有以下 JSON 文档：

{
  "company": {
    "name": "Acme Corp",
    "departments": [
      {
        "name": "Engineering",
        "employees": [
          {"name": "Eve"},
          {"name": "Frank"}
        ]
      },
      {
        "name": "Sales",
        "employees": [
          {"name": "Grace"},
          {"name": "Heidi"}
        ]
      }
    ]
  }
}

SELECT JSON_EXTRACT(data, '$**.name'); 将返回 ["Acme Corp", "Engineering", "Eve", "Frank", "Sales", "Grace", "Heidi"]。它会找到所有名为 name 的属性，无论它们嵌套在 JSON 结构中的哪个位置。

2.3 数组索引范围：[start to end]

可以使用范围来提取数组中的多个连续元素。 [start to end] 包含 start 和 end 索引位置的元素。

示例：

假设我们有以下 JSON 文档：

{
  "numbers": [10, 20, 30, 40, 50]
}

SQL 语句	返回值	说明
`SELECT JSON_EXTRACT(data, '$.numbers[1 to 3]');`	`[20, 30, 40]`	提取 `numbers` 数组中索引 1 到 3（包含 1 和 3）的元素。
`SELECT JSON_EXTRACT(data, '$.numbers[0 to 1]');`	`[10, 20]`	提取 `numbers` 数组中索引 0 到 1（包含 0 和 1）的元素。

2.4 LAST 关键字

LAST 关键字用于访问数组中的最后一个元素。

示例：

假设我们有以下 JSON 文档：

{
  "items": ["apple", "banana", "cherry"]
}

SELECT JSON_EXTRACT(data, '$.items[LAST]'); 将返回 "cherry"。

2.5 路径表达式中的变量

可以使用变量来动态构建路径表达式。这在需要根据某些条件提取不同数据时非常有用。

示例：

SET @key = 'city';
SELECT JSON_EXTRACT(data, CONCAT('$.address.', @key));

在这个例子中，@key 变量的值为 'city'，因此 CONCAT('$.address.', @key) 将生成路径表达式 '$.address.city'。

3. JSON_EXTRACT() 的实际应用场景

数据提取和转换： 从 JSON 数据中提取特定字段，并将其转换为适合在关系型数据库中存储的格式。
条件查询： 根据 JSON 数据中的值进行过滤和排序。
数据聚合： 对 JSON 数据进行分组和聚合，例如计算某个属性的总和或平均值。
报表生成： 从 JSON 数据中提取所需的数据，并生成报表。

示例：

假设我们有一个名为 orders 的表，其中包含一个名为 order_details 的 JSON 列，存储订单的详细信息。

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

INSERT INTO orders (order_date, order_details) VALUES
('2023-10-26', '{"customer_id": 123, "items": [{"product_id": 1, "quantity": 2}, {"product_id": 2, "quantity": 1}] }'),
('2023-10-27', '{"customer_id": 456, "items": [{"product_id": 3, "quantity": 3}] }');

以下是一些使用 JSON_EXTRACT() 的示例查询：

提取所有订单的客户 ID：

SELECT id, JSON_EXTRACT(order_details, '$.customer_id') AS customer_id FROM orders;

提取所有订单的商品信息：

SELECT id, JSON_EXTRACT(order_details, '$.items') AS items FROM orders;

查询客户 ID 为 123 的订单：

SELECT id, order_date
FROM orders
WHERE JSON_EXTRACT(order_details, '$.customer_id') = 123;

计算每个订单中的商品总数（需要用到 JSON_TABLE 函数，后续会讲解）：

SELECT
    o.id,
    SUM(jt.quantity) AS total_quantity
FROM
    orders o
    CROSS JOIN JSON_TABLE(
        o.order_details,
        '$.items[*]'
        COLUMNS (
            quantity INT PATH '$.quantity'
        )
    ) jt
GROUP BY
    o.id;

4. JSON_TABLE() 函数与 JSON_EXTRACT() 的配合使用

JSON_TABLE() 函数可以将 JSON 数组转换为关系型表格，从而可以更方便地对 JSON 数据进行处理。它经常与 JSON_EXTRACT() 函数配合使用，以提取更复杂的数据。

JSON_TABLE() 的基本语法：

JSON_TABLE(
    json_doc,
    path,
    COLUMNS (
      column_name data_type PATH path_expression,
      ...
    )
) AS alias

json_doc: 包含 JSON 数据的字符串。
path: 用于指定要转换为表格的 JSON 数组的路径表达式。
COLUMNS: 定义表格的列和数据类型，以及从 JSON 数组中提取数据的路径表达式。
alias: 表格的别名。

示例：

在上面的 orders 表的例子中，我们可以使用 JSON_TABLE() 函数来提取每个订单中的商品信息，并将其转换为表格：

SELECT
    o.id,
    jt.product_id,
    jt.quantity
FROM
    orders o
    CROSS JOIN JSON_TABLE(
        o.order_details,
        '$.items[*]'
        COLUMNS (
            product_id INT PATH '$.product_id',
            quantity INT PATH '$.quantity'
        )
    ) jt;

这个查询将返回一个包含订单 ID、商品 ID 和商品数量的表格。

5. JSON_UNQUOTE() 函数：去除引号

JSON_EXTRACT() 函数返回的值通常是带引号的字符串。如果需要去除这些引号，可以使用 JSON_UNQUOTE() 函数。

示例：

SELECT JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) AS name FROM your_table;

如果 data 列包含 {"name": "Alice"}，则这个查询将返回 Alice，而不是 "Alice"。

6. JSON_CONTAINS() 函数：检查 JSON 文档是否包含指定元素

JSON_CONTAINS() 函数检查 JSON 文档是否包含指定的 JSON 片段。它返回 1 (TRUE) 如果包含，否则返回 0 (FALSE)。

语法：

JSON_CONTAINS(target, candidate[, path])

target: 要搜索的 JSON 文档。
candidate: 要查找的 JSON 片段。
path: (可选) 在 target 中搜索 candidate 的路径。如果省略，则在整个 target 中搜索。

示例：

SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 3}}', '{"a": 1}'); -- 返回 1
SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 3}}', '{"d": 3}', '$.c'); -- 返回 1
SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 3}}', '{"a": 2}'); -- 返回 0

7. 高级技巧与注意事项

性能优化： 避免在 WHERE 子句中使用复杂的 JSON_EXTRACT() 表达式，因为这可能会导致全表扫描。尽可能使用索引来提高查询性能。可以考虑创建虚拟列并在该虚拟列上创建索引。
错误处理： 当路径表达式无效或 JSON 数据格式不正确时，JSON_EXTRACT() 函数可能会返回 NULL 或引发错误。务必进行适当的错误处理，例如使用 COALESCE() 函数来提供默认值。
数据类型转换： JSON_EXTRACT() 函数返回的值始终是字符串。如果需要将其转换为其他数据类型（例如整数或日期），可以使用 CAST() 函数。
MySQL 版本兼容性： JSON 函数是在 MySQL 5.7.22 中引入的。如果使用的是较早的版本，则需要升级 MySQL。
避免过度嵌套： 尽量避免在 JSON 文档中使用过度嵌套的结构，因为这会使路径表达式变得复杂，并降低查询性能。

代码示例总结

这里我们把一些关键的代码示例放到一起，方便快速查阅：

-- 基本路径表达式示例
SELECT JSON_EXTRACT(data, '$.name');
SELECT JSON_EXTRACT(data, '$.address.city');
SELECT JSON_EXTRACT(data, '$.hobbies[0]');

-- 通配符示例
SELECT JSON_EXTRACT(data, '$.employees[*].name');
SELECT JSON_EXTRACT(data, '$**.name');

-- 数组索引范围示例
SELECT JSON_EXTRACT(data, '$.numbers[1 to 3]');

-- LAST 关键字示例
SELECT JSON_EXTRACT(data, '$.items[LAST]');

-- 变量示例
SET @key = 'city';
SELECT JSON_EXTRACT(data, CONCAT('$.address.', @key));

-- JSON_TABLE() 示例
SELECT
    o.id,
    jt.product_id,
    jt.quantity
FROM
    orders o
    CROSS JOIN JSON_TABLE(
        o.order_details,
        '$.items[*]'
        COLUMNS (
            product_id INT PATH '$.product_id',
            quantity INT PATH '$.quantity'
        )
    ) jt;

-- JSON_UNQUOTE() 示例
SELECT JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) AS name FROM your_table;

-- JSON_CONTAINS() 示例
SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 3}}', '{"a": 1}');
SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 3}}', '{"d": 3}', '$.c');

掌握 JSON 数据的提取和查询

JSON_EXTRACT() 函数及其路径表达式是 MySQL 中处理 JSON 数据的强大工具。熟练掌握这些技术，可以让我们更有效地从 JSON 文档中提取所需数据，并将其用于各种应用场景。结合 JSON_TABLE()、JSON_UNQUOTE() 和 JSON_CONTAINS() 等其他 JSON 函数，可以实现更复杂的数据处理任务。

MySQL JSON_EXTRACT() 函数详解：路径表达式的艺术

发表回复 取消回复

MySQL `JSON_EXTRACT()` 函数详解：路径表达式的艺术

发表回复取消回复