Return result set as a single JSON array.
MySQL Function: JSON_ARRAYAGG()
Summary: in this tutorial, you will learn how to use the MySQL JSON_ARRAYAGG()function to aggregate values into a JSON array.
Introduction to MySQL JSON_ARRAYAGG() function
The JSON_ARRAYAGG() function is used to aggregate values into a JSON array.
Here’s the syntax of the JSON_ARRAYAGG() function:
JSON_ARRAYAGG(value)Code language: SQL (Structured Query Language) (sql)In this syntax:
value: This value can be an expression or a column whose values you want to aggregate into a JSON array.
The JSON_ARRAYAGG() function returns a JSON array whose elements consist of the values. The order of elements in the resulting array is undefined.
If the column has no rows, the JSON_ARRAYAGG() function returns NULL. If the value is NULL, the function returns an array that contains null elements.
In practice, you often use the JSON_ARRAYAGG() function with the GROUP BY clause to create JSON arrays for each group of rows based on a column or a set of columns.
MySQL JSON_ARRAYAGG() function example
We’ll use the employees and customers tables from the sample database for the demonstration:

The following query retrieves data from the customers and employees tables, and uses the CONCAT_WS() and JSON_ARRAYAGG() functions to generate a result set that includes a list of sales employees and the customer numbers associated with each of them.
SELECT
CONCAT_WS(' ', firstName, lastName) salesEmployee,
JSON_ARRAYAGG(customerNumber) customerNumbers
FROM
customers c
INNER JOIN employees e ON c.salesRepEmployeeNumber = e.employeeNumber
GROUP BY
salesRepEmployeeNumber
ORDER BY
salesEmployee;Code language: SQL (Structured Query Language) (sql)Output:
+------------------+----------------------------------------------------+
| salesEmployee | customerNumbers |
+------------------+----------------------------------------------------+
| Andy Fixter | [114, 276, 282, 333, 471] |
| Barry Jones | [121, 128, 144, 167, 189, 259, 299, 415, 448] |
| Foon Yue Tseng | [151, 168, 181, 233, 424, 455, 456] |
| George Vanauf | [131, 175, 202, 260, 319, 328, 447, 486] |
| Gerard Hernandez | [103, 119, 141, 171, 209, 242, 256] |
| Julie Firrelli | [173, 204, 320, 339, 379, 495] |
| Larry Bott | [186, 187, 201, 240, 311, 324, 334, 489] |
| Leslie Jennings | [124, 129, 161, 321, 450, 487] |
| Leslie Thompson | [112, 205, 219, 239, 347, 475] |
| Loui Bondur | [146, 172, 250, 350, 353, 406] |
| Mami Nishi | [148, 177, 211, 385, 398] |
| Martin Gerard | [216, 298, 344, 376, 458, 484] |
| Pamela Castillo | [145, 227, 249, 278, 314, 381, 382, 386, 452, 473] |
| Peter Marsh | [166, 323, 357, 412, 496] |
| Steve Patterson | [157, 198, 286, 362, 363, 462] |
+------------------+----------------------------------------------------+
15 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Here’s a step-by-step explanation of the query:
CONCAT_WS(' ', firstName, lastName) salesEmployee: This uses the CONCAT_WS() function to combine the values in thefirstNameandlastNamecolumns from thecustomerstable, separated by a space. It creates a new column calledsalesEmployeethat contains the full name of the sales employees.JSON_ARRAYAGG(customerNumber) customerNumbers: This uses theJSON_ARRAYAGG()function to aggregate the values in thecustomerNumbercolumn from thecustomerstable into a JSON array. Each sales employee’s list of customer numbers is stored in a column calledcustomerNumbers.FROM customers c INNER JOIN employees e ON c.salesRepEmployeeNumber = e.employeeNumber: This clause specifies the tables involved in the query and sets up an inner join between thecustomerstable (aliased as'c') and theemployeestable (aliased as'e'). The join is based on thesalesRepEmployeeNumbercolumn in thecustomerstable and theemployeeNumbercolumn in theemployeestable.GROUP BY salesRepEmployeeNumber: This GROUP BY clause groups the results by thesalesRepEmployeeNumberfrom thecustomerstable. This means that the aggregation functions will group data for each unique sales representative.ORDER BY salesEmployee: This ORDER BY clause sorts the results by thesalesEmployeecolumn, which contains the full name of the sales employee.
In short, the query retrieves a list of sales employees and, for each sales employee, aggregates the customer numbers associated with them into a JSON array. The result set includes one row for each sales employee, with their full name and a JSON array of customer numbers they are responsible for. The result set is sorted alphabetically by the sales employee’s full name.
Summary
- Use the
JSON_ARRAYAGG()function to aggregate values into a JSON array.
