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

目录

1. 基本知识

JSON_ARRAYAGG

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

JSON 数组

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

其语法结构如下：

JSON_ARRAYAGG(expression [ORDERBY...])

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

2. Demo

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

示例如下：

CREATETABLE employees (
    id INTPRIMARYKEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10,2));INSERTINTO 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 ORDERBY salary DESC)AS employees_names
FROM employees;

截图如下：

2.2 带排序聚合

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

SELECT JSON_ARRAYAGG(name ORDERBY 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 touse 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
    ORDERBY salary DESC)AS sorted_employees;

截图如下：

2.2.2 创建临时表

-- 创建临时表CREATETEMPORARYTABLE sorted_employees ASSELECT name
FROM employees
ORDERBY salary DESC;-- 对临时表进行聚合SELECT JSON_ARRAYAGG(name)AS employees_names
FROM sorted_employees;-- 删除临时表DROPTEMPORARYTABLE 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
GROUPBY 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}
        ]
    }
]

截图如下：