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 the firstName and lastName columns from the customers table, separated by a space. It creates a new column called salesEmployee that contains the full name of the sales employees.
• JSON_ARRAYAGG(customerNumber) customerNumbers: This uses the JSON_ARRAYAGG() function to aggregate the values in the customerNumbercolumn from the customers table into a JSON array. Each sales employee’s list of customer numbers is stored in a column called customerNumbers.
• 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 the customerstable (aliased as 'c') and the employees table (aliased as 'e'). The join is based on the salesRepEmployeeNumber column in the customers table and the employeeNumber column in the employees table.
• GROUP BY salesRepEmployeeNumber: This GROUP BY clause groups the results by the salesRepEmployeeNumber from the customers table. 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 the salesEmployee column, 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.