详细分析Mysql中的 JSON_ARRAYAGG 基本知识（附Demo）

目录

• 1. 基本知识
• 2. Demo
• • 2.1 简单聚合
  • 2.2 带排序聚合
  • • 2.2.1 子查询进行排序
    • 2.2.2 创建临时表
  • 2.3 带条件聚合
  • 2.4 多列聚合
  • 2.5 嵌套 JSON 结构

1. 基本知识

JSON_ARRAYAGG为 SQL 聚合函数，用于将一组值聚合为一个 JSON 数组

• 多行结果组合成一个 JSON 数组形式的场景中非常有用
• JSON_ARRAYAGG 可以与其他 JSON 处理函数（如 JSON_OBJECTAGG）结合使用，以构建复杂的 JSON 结构

其语法结构如下：

JSON_ARRAYAGG(expression [ORDER BY ...])

• expression：要聚合的列或表达式
• ORDER BY：可选，指定聚合值的排序顺序

2. Demo

为更好的加深印象，以Demo的方式进行展示

示例如下：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice', 'Engineering', 60000),
(2, 'Bob', 'Engineering', 70000),
(3, 'Charlie', 'HR', 50000),
(4, 'David', 'Engineering', 80000),
(5, 'Eve', 'HR', 55000);

由于我的Navicat版本较低，无法输出JSON_ARRAYAGG的相关结果

• 要么使用其他工具，要么升级Navicat软件（后续以命令行的结果进行展示）

2.1 简单聚合

SELECT JSON_ARRAYAGG(name ORDER BY salary DESC) AS employees_names
FROM employees;

截图如下：

2.2 带排序聚合

内部嵌套排序，有些数据库是不支持的，即使8的版本号

SELECT JSON_ARRAYAGG(name ORDER BY salary DESC) AS employees_names

会输出如下：

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY salary DESC) AS employees_names
FROM employees' at line 1

对于上述情况，使用如下方式进行处理

2.2.1 子查询进行排序

SELECT JSON_ARRAYAGG(name) AS employees_names
FROM (
    SELECT name
    FROM employees
    ORDER BY salary DESC
) AS sorted_employees;

截图如下：

2.2.2 创建临时表

-- 创建临时表
CREATE TEMPORARY TABLE sorted_employees AS
SELECT name
FROM employees
ORDER BY salary DESC;

-- 对临时表进行聚合
SELECT JSON_ARRAYAGG(name) AS employees_names
FROM sorted_employees;

-- 删除临时表
DROP TEMPORARY TABLE sorted_employees;

截图如下：

2.3 带条件聚合

SELECT JSON_ARRAYAGG(name) AS engineering_employees
FROM employees
WHERE department = 'Engineering';

截图如下：

2.4 多列聚合

将员工的 name 和 salary 作为对象聚合为一个 JSON 数组

SELECT JSON_ARRAYAGG(JSON_OBJECT('name', name, 'salary', salary)) AS employees_info
FROM employees;

#输出如下：
{"employees_info": [
    {"name": "Alice", "salary": 60000},
    {"name": "Bob", "salary": 70000},
    {"name": "Charlie", "salary": 50000},
    {"name": "David", "salary": 80000},
    {"name": "Eve", "salary": 55000}
]}

截图如下：

2.5 嵌套 JSON 结构

按部门聚合员工信息：

SELECT department, JSON_ARRAYAGG(JSON_OBJECT('name', name, 'salary', salary)) AS employees
FROM employees
GROUP BY department;

# 输出如下
[
    {
        "department": "Engineering",
        "employees": [
            {"name": "Alice", "salary": 60000},
            {"name": "Bob", "salary": 70000},
            {"name": "David", "salary": 80000}
        ]
    },
    {
        "department": "HR",
        "employees": [
            {"name": "Charlie", "salary": 50000},
            {"name": "Eve", "salary": 55000}
        ]
    }
]

截图如下：