MySQL高级函数之：`JSON_ARRAYAGG()`：其在`GROUP BY`中聚合`JSON`数组。

MySQL高级函数：JSON_ARRAYAGG() 深度解析

大家好，今天我们来深入探讨 MySQL 中一个非常强大的 JSON 函数：JSON_ARRAYAGG()。它主要用于在 GROUP BY 操作中，将结果聚合为一个 JSON 数组。通过学习 JSON_ARRAYAGG()，我们可以更灵活地处理和呈现分组后的数据，从而简化复杂的查询和数据转换。

1. JSON_ARRAYAGG() 的基本概念

JSON_ARRAYAGG() 函数的作用是将一个分组内的多个值聚合为一个 JSON 数组。它的基本语法如下：

JSON_ARRAYAGG([DISTINCT] expr [ORDER BY {col_name | expr} [ASC | DESC], ...])

• expr：要聚合的表达式或列名。
• DISTINCT：可选，用于去除重复值。
• ORDER BY：可选，用于指定数组内元素的排序方式。可以指定多个排序字段和排序方向。

简单来说，JSON_ARRAYAGG() 接受一个表达式，然后将分组内的所有该表达式的值收集起来，构建成一个 JSON 数组。如果指定了 DISTINCT，则数组中只包含不同的值。如果指定了 ORDER BY，则数组中的元素会按照指定的顺序排列。

2. 准备工作：创建示例表和数据

为了更好地演示 JSON_ARRAYAGG() 的用法，我们先创建一个示例表 products，并插入一些数据。

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  category VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);

INSERT INTO products (category, name, price) VALUES
('Electronics', 'Laptop', 1200.00),
('Electronics', 'Smartphone', 800.00),
('Electronics', 'Tablet', 300.00),
('Clothing', 'T-Shirt', 25.00),
('Clothing', 'Jeans', 75.00),
('Clothing', 'Jacket', 150.00),
('Home Goods', 'Sofa', 800.00),
('Home Goods', 'Table', 200.00),
('Home Goods', 'Chair', 100.00),
('Food', 'Apple', 1.00),
('Food', 'Banana', 0.50),
('Food', 'Orange', 0.75),
('Food', 'Apple', 1.00);

现在，products 表包含了各种商品的信息，包括类别、名称和价格。

3. JSON_ARRAYAGG() 的基本应用：按类别聚合商品名称

最简单的用法是将每个类别的商品名称聚合到一个 JSON 数组中。

SELECT
  category,
  JSON_ARRAYAGG(name) AS product_names
FROM
  products
GROUP BY
  category;

这个查询会返回每个类别及其对应的商品名称数组。例如，对于 "Electronics" 类别，结果可能如下：

[
  "Laptop",
  "Smartphone",
  "Tablet"
]

4. 使用 DISTINCT 去重

如果我们需要去除重复的商品名称，可以使用 DISTINCT 关键字。

SELECT
  category,
  JSON_ARRAYAGG(DISTINCT name) AS product_names
FROM
  products
GROUP BY
  category;

在这个例子中，如果某个类别有重复的商品名称，它们只会在 JSON 数组中出现一次。 比如 Food 类别， Apple出现了两次， 使用DISTINCT后，数组中只会保留一个Apple。

5. 使用 ORDER BY 排序

我们可以使用 ORDER BY 子句来控制 JSON 数组中元素的顺序。例如，按照商品名称的字母顺序排序。

SELECT
  category,
  JSON_ARRAYAGG(name ORDER BY name) AS product_names
FROM
  products
GROUP BY
  category;

这会按照商品名称的字母顺序对每个类别的商品名称进行排序。我们也可以使用 DESC 关键字进行降序排列。

SELECT
  category,
  JSON_ARRAYAGG(name ORDER BY name DESC) AS product_names
FROM
  products
GROUP BY
  category;

6. 更复杂的数据结构：聚合包含多个字段的 JSON 对象

JSON_ARRAYAGG() 不仅可以聚合简单的值，还可以聚合复杂的 JSON 对象。我们可以使用 JSON_OBJECT() 函数来构建包含多个字段的 JSON 对象。

SELECT
  category,
  JSON_ARRAYAGG(
    JSON_OBJECT('name', name, 'price', price)
  ) AS products_info
FROM
  products
GROUP BY
  category;

这个查询会返回每个类别及其对应的商品信息数组，每个商品信息是一个包含 "name" 和 "price" 字段的 JSON 对象。 例如，对于 "Electronics" 类别，结果可能如下：

[
  {"name": "Laptop", "price": 1200.00},
  {"name": "Smartphone", "price": 800.00},
  {"name": "Tablet", "price": 300.00}
]

7. 嵌套的 JSON_ARRAYAGG()：更复杂的分组和聚合

我们可以嵌套使用 JSON_ARRAYAGG() 函数，以实现更复杂的分组和聚合。例如，我们可以先按照类别分组，然后再按照价格范围分组，最后将每个价格范围内的商品名称聚合到一个 JSON 数组中。

首先，我们需要创建一个辅助表或者使用子查询来定义价格范围。这里我们使用一个简单的 CASE 语句来定义价格范围。

SELECT
  category,
  CASE
    WHEN price < 50 THEN 'Under 50'
    WHEN price >= 50 AND price < 200 THEN '50-200'
    ELSE 'Over 200'
  END AS price_range,
  JSON_ARRAYAGG(name) AS product_names
FROM
  products
GROUP BY
  category,
  price_range
ORDER BY
  category,
  price_range;

这个查询会按照类别和价格范围对商品进行分组，并将每个价格范围内的商品名称聚合到一个 JSON 数组中。现在，假设我们想要将每个类别的所有价格范围及其对应的商品名称数组聚合到一个 JSON 对象中，可以使用嵌套的 JSON_ARRAYAGG()。

SELECT
  category,
  JSON_OBJECT(
    GROUP_CONCAT(price_range),  -- 避免重复键
    JSON_ARRAYAGG(product_names)
  ) AS category_info
FROM (
  SELECT
    category,
    CASE
      WHEN price < 50 THEN 'Under 50'
      WHEN price >= 50 AND price < 200 THEN '50-200'
      ELSE 'Over 200'
    END AS price_range,
    JSON_ARRAYAGG(name) AS product_names
  FROM
    products
  GROUP BY
    category,
    price_range
) AS subquery
GROUP BY
  category;

注意： 上面的代码存在一个问题。 JSON_OBJECT 要求键是唯一的，而我们直接使用 price_range 作为键，会导致重复的键覆盖前面的值。 为了解决这个问题，我们使用 GROUP_CONCAT 将 price_range 连接成一个字符串，避免键重复。但是这种方式不太优雅，更好的方式是使用 JSON_ARRAYAGG(JSON_OBJECT()) 来构建 JSON 对象数组。

正确的代码如下：

SELECT
    category,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'price_range', price_range,
            'product_names', product_names
        )
    ) AS category_info
FROM (
    SELECT
        category,
        CASE
            WHEN price < 50 THEN 'Under 50'
            WHEN price >= 50 AND price < 200 THEN '50-200'
            ELSE 'Over 200'
        END AS price_range,
        JSON_ARRAYAGG(name) AS product_names
    FROM
        products
    GROUP BY
        category,
        price_range
) AS subquery
GROUP BY
    category;

这个查询会返回每个类别及其对应的价格范围信息数组，每个价格范围信息是一个包含 "price_range" 和 "product_names" 字段的 JSON 对象。 例如，对于 "Electronics" 类别，结果可能如下：

[
  {
    "price_range": "Over 200",
    "product_names": ["Laptop"]
  },
  {
    "price_range": "50-200",
    "product_names": ["Smartphone"]
  },
  {
    "price_range": "Under 50",
    "product_names": ["Tablet"]
  }
]

8. JSON_ARRAYAGG() 与 NULL 值的处理

当聚合的表达式的值为 NULL 时，JSON_ARRAYAGG() 会如何处理呢？ 默认情况下，JSON_ARRAYAGG() 会忽略 NULL 值。 也就是说，NULL 值不会包含在生成的 JSON 数组中。

为了演示这一点，我们先向 products 表中插入一些包含 NULL 值的记录。

INSERT INTO products (category, name, price) VALUES
('Electronics', 'Accessory', NULL),
('Clothing', NULL, 10.00);

然后，我们执行以下查询：

SELECT
  category,
  JSON_ARRAYAGG(name) AS product_names,
  JSON_ARRAYAGG(price) AS product_prices
FROM
  products
GROUP BY
  category;

在这个查询中，"Electronics" 类别的 product_names 数组不会包含 NULL 值，而 "Clothing" 类别的 product_names 数组也不会包含 NULL 值。"Electronics" 类别的 product_prices 数组会包含 [1200.00, 800.00, 300.00, null]。

9. JSON_ARRAYAGG() 的性能考虑

当处理大量数据时，JSON_ARRAYAGG() 的性能可能会成为一个问题。 因为它需要将分组内的所有值都加载到内存中，然后构建 JSON 数组。 为了提高性能，可以考虑以下几点：

• 优化查询条件： 尽量减少需要聚合的数据量。
• 使用索引： 在分组字段和排序字段上创建索引，可以加快查询速度。
• 限制结果集大小： 如果只需要部分数据，可以使用 LIMIT 子句限制结果集大小。
• 考虑替代方案： 在某些情况下，可以使用其他方法来实现相同的功能，例如使用存储过程或应用程序代码来处理数据。

10. 一些注意事项

• MySQL 5.7.22 及更高版本才支持 ORDER BY 子句。
• JSON_ARRAYAGG() 生成的 JSON 数组的最大大小受到 max_allowed_packet 系统变量的限制。
• 在使用 JSON_OBJECT() 函数构建 JSON 对象时，要确保键是唯一的，否则后面的值会覆盖前面的值。

示例：使用JSON_ARRAYAGG 和JSON_OBJECT构建更复杂的JSON数据

假设我们需要查询每个类别下的产品信息，并且将产品信息以JSON对象的形式存储在数组中，同时需要统计每个类别下产品的总价格。

SELECT
    p.category,
    JSON_ARRAYAGG(JSON_OBJECT('name', p.name, 'price', p.price)) AS products,
    SUM(p.price) AS total_price
FROM
    products p
GROUP BY
    p.category;

该查询会返回每个类别及其对应的产品信息数组和总价格。例如，对于 “Electronics” 类别，结果可能如下：

{
    "category": "Electronics",
    "products": [
        {"name": "Laptop", "price": 1200.00},
        {"name": "Smartphone", "price": 800.00},
        {"name": "Tablet", "price": 300.00},
        {"name": "Accessory", "price": null}
    ],
    "total_price": 2300.00
}

总结

JSON_ARRAYAGG() 是一个强大的 MySQL 函数，可以方便地将分组后的数据聚合为 JSON 数组。 通过结合 DISTINCT、ORDER BY 和 JSON_OBJECT() 等函数，我们可以构建更复杂的数据结构，从而简化复杂的查询和数据转换。 但是，在使用 JSON_ARRAYAGG() 时，也要注意性能问题，并根据实际情况选择合适的优化策略。

这个函数在数据分析，报表生成，API数据接口等场景下具有广泛的应用。 掌握它，可以更高效的完成数据处理任务。