The JSON_ARRAYAGG function in MySQL is used to aggregate values into a JSON array. It is particularly useful when you want to combine multiple rows of data into a single JSON array. This function was introduced in MySQL version 5.7.22.

Syntax

The basic syntax for the JSON_ARRAYAGG function is as follows:

JSON_ARRAYAGG(expr)

expr: The expression or column whose values you want to aggregate into a JSON array.

Example

Let’s say you have a table named employees with the following structure:

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    department VARCHAR(50)
);

And it contains the following data:

INSERT INTO employees (id, name, department) VALUES
(1, 'John Doe', 'HR'),
(2, 'Jane Smith', 'IT'),
(3, 'Bob Johnson', 'Finance');

Now, suppose you want to aggregate the names of employees in a JSON array based on their department. You can use the JSON_ARRAYAGG function to achieve this:

SELECT department,
JSON_ARRAYAGG(name) AS employee_names
FROM employees
GROUP BY department;

The result would be:

+------------+------------------------+
| department | employee_names         |
+------------+------------------------+
| HR         | ["John Doe"]           |
| IT         | ["Jane Smith"]         |
| Finance    | ["Bob Johnson"]        |
+------------+------------------------+

In this example, the JSON_ARRAYAGG function is used to create a JSON array (employee_names) for each department, containing the names of employees belonging to that department. The GROUP BY clause ensures that the aggregation is done on a per-department basis.

Keep in mind that the actual JSON format may vary depending on the MySQL version and configuration. The above example assumes a standard JSON format.