MariaDB JSON_ARRAYAGG() Function

In MariaDB, JSON_ARRAYAGG() is a built-in function that aggregates the value of a specified column or expression into a JSON array.

MariaDB JSON_ARRAYAGG() Syntax

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

JSON_ARRAYAGG(
  [DISTINCT] expr [,expr ...]
  [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]]
  [LIMIT {[offset,] row_count | row_count OFFSET offset}]
)

We generally use the JSON_ARRAYAGG() function like this:

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 MariaDB JSON_ARRAYAGG(expr) function returns a JSON array that aggregates all matching values.

This function will return NULL if the result set does not have any rows.

MariaDB JSON_ARRAYAGG() Examples

We’ll demonstrate an example with the student_score table.

First, let’s create the table named student_score 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 create a table to store the grades of students, this table consists of 4 columns:

• id is the primary key
• name is the student’s first name
• subject is the student’s subject
• score is the student’s grade

Second, let me 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 |
+----+------+---------+-------+

Basic example

To get a JSON array containing all the subjects you want, use the following statement:

SELECT
    JSON_ARRAYAGG(subject) AS `Subjects`
FROM student_score;

Output:

+------------------------------+
| Subjects                     |
+------------------------------+
| ["Math","English","English"] |
+------------------------------+

You may notice that there are duplicate names in the returned results. You can use the DISTINCT keyword to get unique values:

SELECT
    JSON_ARRAYAGG(DISTINCT subject) AS `Subjects`
FROM student_score;

Output:

+--------------------+
| Subjects           |
+--------------------+
| ["English","Math"] |
+--------------------+

Group

By grouping, we can use the JSON_ARRAYAGG() function to get a list of the subjects for each person, as follows:

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

Output:

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

In this example, we group all rows by name.

We know that Tom took both Math and English exams, while Tim only took the English exam.

Conclusion

In MariaDB, JSON_ARRAYAGG() is a built-in function that aggregates the value of a specified column or expression into a JSON array.