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

MySQL 高级函数 `JSON_EXTRACT()`：JSON 路径表达式高级用法

大家好，今天我们要深入探讨 MySQL 中处理 JSON 数据的一个核心函数：JSON_EXTRACT()。虽然 JSON_EXTRACT() 函数的基本用法相对简单，但要真正发挥它的强大之处，需要掌握其 JSON 路径表达式的高级用法。本次讲座将围绕以下几个方面展开：

JSON_EXTRACT() 函数基础回顾
JSON 路径表达式语法详解
高级 JSON 路径表达式应用场景及示例
JSON_EXTRACT() 与其他 JSON 函数的结合使用
性能优化建议

1. `JSON_EXTRACT()` 函数基础回顾

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

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

json_doc：包含 JSON 数据的字符串或列。
path：JSON 路径表达式，指定要提取的数据在 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}'),
(2, 'Smartphone', '{"brand": "Samsung", "model": "Galaxy S23", "price": 900}'),
(3, 'Tablet', '{"brand": "Apple", "model": "iPad Pro", "price": 1000}');

要提取 Laptop 的品牌，可以使用以下查询：

SELECT JSON_EXTRACT(details, '$.brand') AS brand FROM products WHERE id = 1;

结果：

+-------+
| brand |
+-------+
| "Dell" |
+-------+

注意，JSON_EXTRACT() 返回的是 JSON 字符串，即使提取的是单个值。如果需要将结果转换为特定类型，可以使用 JSON_UNQUOTE() 函数：

SELECT JSON_UNQUOTE(JSON_EXTRACT(details, '$.brand')) AS brand FROM products WHERE id = 1;

结果：

+-------+
| brand |
+-------+
| Dell  |
+-------+

2. `JSON` 路径表达式语法详解

JSON 路径表达式使用 $ 符号表示 JSON 文档的根节点。路径中的每个后续元素用 . 分隔，用于访问对象中的键，用 [] 分隔，用于访问数组中的索引。

2.1 基本路径表达式：

$.key: 访问 JSON 对象中名为 "key" 的属性。
$[index]: 访问 JSON 数组中索引为 "index" 的元素 (索引从 0 开始)。

2.2 通配符：

*: 匹配对象中所有键或数组中所有索引。
**: 递归地匹配对象中所有键，可以用来查找深层嵌套的属性。

2.3 范围：

[start, end]: 访问数组中从索引 start 到 end (包含 end) 的元素。
[start to end]: 访问数组中从索引 start 到 end (包含 end) 的元素，与[start, end]相同，为了兼容性。
[start,]: 访问数组中从索引 start 到末尾的元素。
[,end]: 访问数组中从索引 0 到 end (包含 end) 的元素。

2.4 路径表达式中的变量：

虽然 JSON_EXTRACT 本身不支持直接在路径中使用变量，但可以通过字符串拼接的方式构造动态路径。

2.5 特殊字符转义：

如果键名包含特殊字符（例如空格或点号），需要用双引号将键名括起来。例如，如果 JSON 对象包含一个名为 "my.key" 的属性，可以使用 $"my.key" 来访问它。

示例：

假设我们有以下 JSON 数据存储在 data 列中：

{
  "name": "Product A",
  "details": {
    "manufacturer": "Acme Corp",
    "specs": {
      "weight": "1.2kg",
      "dimensions": [10, 20, 30]
    }
  },
  "tags": ["electronics", "gadget", "featured"]
}

路径表达式	描述	返回值
`$.name`	提取 "name" 属性的值。	`"Product A"`
`$.details.manufacturer`	提取 "details" 对象中 "manufacturer" 属性的值。	`"Acme Corp"`
`$.details.specs.weight`	提取 "details" 对象中 "specs" 对象中 "weight" 属性的值。	`"1.2kg"`
`$.details.specs.dimensions[0]`	提取 "details" 对象中 "specs" 对象中 "dimensions" 数组的第一个元素。	`10`
`$.tags[1]`	提取 "tags" 数组的第二个元素。	`"gadget"`
`$.tags[*]`	提取 "tags" 数组的所有元素。	`["electronics", "gadget", "featured"]`
`$.details.specs.dimensions[*]`	提取 "dimensions" 数组的所有元素。	`[10, 20, 30]`
`$.**weight`	递归查找名为 "weight" 的属性。	`"1.2kg"`
`$.tags[0 to 1]`	提取 "tags" 数组中索引 0 到 1 的元素。	`["electronics", "gadget"]`

3. 高级 `JSON` 路径表达式应用场景及示例

3.1 提取数组中的特定范围：

假设我们有一个存储订单信息的表，order_items 列存储 JSON 数组，包含订单中所有商品的名称：

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

INSERT INTO orders (id, order_date, order_items) VALUES
(1, '2023-10-26', '["Product A", "Product B", "Product C", "Product D"]'),
(2, '2023-10-27', '["Product E", "Product F"]');

要提取每个订单的前两个商品，可以使用以下查询：

SELECT id, JSON_EXTRACT(order_items, '$[0 to 1]') AS first_two_items FROM orders;

结果：

+----+----------------------+
| id | first_two_items      |
+----+----------------------+
|  1 | ["Product A", "Product B"] |
|  2 | ["Product E", "Product F"] |
+----+----------------------+

3.2 递归查找嵌套属性：

假设我们有一个复杂的 JSON 结构，其中某些属性可能嵌套在多层对象中。使用 ** 通配符可以轻松找到这些属性。

例如，假设 data 列包含以下 JSON 数据：

{
  "level1": {
    "level2": {
      "level3": {
        "target_value": "Found!"
      }
    }
  },
  "another_level1": {
    "target_value": "Also Found!"
  }
}

要找到所有名为 target_value 的属性，可以使用以下查询：

SELECT JSON_EXTRACT(data, '$**.target_value') AS target_values FROM your_table;

结果：

+------------------------------------------------------------------+
| target_values                                                    |
+------------------------------------------------------------------+
| ["Found!", "Also Found!"]                                        |
+------------------------------------------------------------------+

3.3 提取 JSON 数组中的所有值到单独的行：

MySQL 8.0 引入了 JSON_TABLE() 函数，可以与 JSON_EXTRACT() 结合使用，将 JSON 数组中的每个元素提取到单独的行中。

例如，假设 products 表的 tags 列存储 JSON 数组：

UPDATE products SET details = JSON_SET(details, '$.tags', JSON_ARRAY('electronic', 'new', 'sale')) WHERE id = 1;
UPDATE products SET details = JSON_SET(details, '$.tags', JSON_ARRAY('mobile', 'popular')) WHERE id = 2;

SELECT id, details FROM products;

现在 details 列中的 tags 属性是JSON数组。要将每个产品的标签提取到单独的行中，可以使用以下查询：

SELECT
    p.id,
    t.tag
FROM
    products p,
    JSON_TABLE(p.details -> '$.tags', '$[*]' COLUMNS (tag VARCHAR(255) PATH '$')) AS t;

结果：

+----+------------+
| id | tag        |
+----+------------+
|  1 | electronic |
|  1 | new        |
|  1 | sale       |
|  2 | mobile     |
|  2 | popular    |
+----+------------+

3.4 使用动态路径表达式：

虽然 JSON_EXTRACT() 本身不支持直接在路径中使用变量，但可以通过字符串拼接构造动态路径。

例如，假设你想根据存储在 config 表中的键名提取 JSON 数据。

CREATE TABLE config (
  id INT PRIMARY KEY,
  key_name VARCHAR(255),
  json_path VARCHAR(255)
);

INSERT INTO config (id, key_name, json_path) VALUES
(1, 'product_name', '$.name'),
(2, 'product_price', '$.details.price');

现在，你可以使用以下查询来动态提取数据：

SELECT
    c.key_name,
    JSON_EXTRACT(p.details, c.json_path) AS extracted_value
FROM
    config c
JOIN
    products p ON 1=1;
WHERE p.id = 1;

这个查询将会根据config表中的json_path动态的从products表中提取数据。

4. `JSON_EXTRACT()` 与其他 JSON 函数的结合使用

JSON_EXTRACT() 通常与其他 JSON 函数结合使用，以实现更复杂的数据处理逻辑。

4.1 JSON_UNQUOTE()：

如前所述，JSON_UNQUOTE() 用于去除 JSON_EXTRACT() 返回的 JSON 字符串的引号。

4.2 JSON_CONTAINS()：

JSON_CONTAINS() 函数检查 JSON 文档是否包含指定的路径和值。可以与 JSON_EXTRACT() 结合使用，以过滤符合特定条件的数据。

例如，要查找所有包含 "electronics" 标签的产品，可以使用以下查询：

SELECT * FROM products WHERE JSON_CONTAINS(details, '["electronics"]', '$.tags');

4.3 JSON_SET()、JSON_REPLACE()、JSON_INSERT()：

这些函数用于修改 JSON 文档。可以与 JSON_EXTRACT() 结合使用，先提取数据，然后根据提取的数据修改 JSON 文档。

例如，假设你想将所有价格低于 1000 的产品的 discounted 属性设置为 true。

UPDATE products
SET details = JSON_SET(details, '$.discounted', 'true')
WHERE JSON_EXTRACT(details, '$.price') < 1000; -- 注意这里比较的是字符串，实际应用中可能需要类型转换

4.4 JSON_ARRAYAGG()：

JSON_ARRAYAGG() 函数用于将多个值聚合到一个 JSON 数组中。可以与 JSON_EXTRACT() 结合使用，提取多个行的数据，并将它们组合成一个 JSON 数组。

例如，要获取所有产品的品牌列表，可以使用以下查询：

SELECT JSON_ARRAYAGG(JSON_UNQUOTE(JSON_EXTRACT(details, '$.brand'))) AS brands FROM products;

5. 性能优化建议

处理 JSON 数据时，性能是一个重要的考虑因素。以下是一些优化建议：

避免在 WHERE 子句中使用复杂的 JSON_EXTRACT() 表达式： 这会导致全表扫描。尽可能使用索引或重构查询。
使用 JSON_TABLE() 提取数组数据： JSON_TABLE() 通常比多次调用 JSON_EXTRACT() 性能更好。
在必要时使用虚拟列： 如果经常需要根据 JSON 数据进行过滤或排序，可以创建虚拟列来存储提取的值，并对虚拟列创建索引。
考虑数据类型转换： JSON_EXTRACT() 返回 JSON 字符串。如果需要进行数值比较或排序，请确保将数据转换为适当的类型。
监控查询性能： 使用 EXPLAIN 语句分析查询执行计划，并根据需要进行优化。
选择合适的 JSON 数据类型： MySQL 5.7.22 引入了 JSON 数据类型的二进制存储格式，比 TEXT 或 VARCHAR 更高效。确保使用 JSON 数据类型存储 JSON 数据。

总结：更好地利用 `JSON_EXTRACT()` 函数

我们深入探讨了 JSON_EXTRACT() 函数及其在 JSON 路径表达式中的高级应用。掌握这些技巧可以帮助你更有效地从 MySQL 数据库中的 JSON 数据中提取和处理信息。通过与其它JSON函数的结合，可以发挥它最大的效能。

MySQL 高级函数 JSON_EXTRACT()：JSON 路径表达式高级用法

1. JSON_EXTRACT() 函数基础回顾

2. JSON 路径表达式语法详解

3. 高级 JSON 路径表达式应用场景及示例

4. JSON_EXTRACT() 与其他 JSON 函数的结合使用