MySQL JSON_ARRAYAGG() Function

The MySQL JSON_ARRAYAGG() function aggregates the values ​​of the specified column or expression into a JSON array.

JSON_ARRAYAGG() Syntax

Here is the syntax of the MySQL JSON_ARRAYAGG() function:

JSON_ARRAYAGG(expr)

Typically, we use the JSON_ARRAYAGG() function like:

SELECT JSON_ARRAYAGG(expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

Parameters

expr

Required. It can be a column name or an expression.

Return value

The MySQL JSON_ARRAYAGG(expr) function returns a JSON array that aggregates the values ​​of a specified column or expression from a group.

This function returns NULL if the result set has no rows.

JSON_ARRAYAGG() Examples

We’ll demonstrate with the student_score table .

First, let’s create the student_score table using the following statement:

DROP TABLE IF EXISTS `student_score`;
CREATE TABLE `student_score` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `score` INT NOT NULL
);

Here we have created a table to store students’ scores. This table consists of 4 columns:

  • id - the primary key
  • name - the student’s name
  • subject - the subject
  • score - the student’s grade of a subject

Second, let’s insert some rows for demonstration:

INSERT INTO `student_score` (`name`, `subject`, `score`)
VALUES
    ('Tom', 'Math', 80),
    ('Tom', 'English', 90),
    ('Tim', 'English', 98);

Here is the rows in the table:

+----+------+---------+-------+
| id | name | subject | score |
+----+------+---------+-------+
|  1 | Tom  | Math    |    80 |
|  2 | Tom  | English |    90 |
|  3 | Tim  | English |    98 |
+----+------+---------+-------+

Now, we can use the JSON_ARRAYAGG() function to get a list of subjects for everyone, as follows:

SELECT
    name AS `Name`,
    JSON_ARRAYAGG(subject) AS `Subjects`
FROM student_score
GROUP BY name;

+------+---------------------+
| Name | Subjects            |
+------+---------------------+
| Tim  | ["English"]         |
| Tom  | ["Math", "English"] |
+------+---------------------+

Here, we know that Tom took the Math and English exams, and Tim took the English exam only.