MariaDB JSON_ARRAYAGG(): Syntax, ORDER BY and Examples

MariaDB JSON_ARRAYAGG(expr) collects values from a result set into one JSON array. It is available from MariaDB 10.5. See the official MariaDB JSON_ARRAYAGG reference.

MariaDB `JSON_ARRAYAGG()` Syntax

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

JSON_ARRAYAGG(
  [DISTINCT] 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 function returns an array containing one element for each input value. Use ORDER BY inside the aggregate when the array needs a defined element order.

The maximum returned length in bytes is controlled by group_concat_max_len. The function returns NULL if there are no result rows or an error occurs. MariaDB does not support JSON_ARRAYAGG() as a window function.

MariaDB `JSON_ARRAYAGG()` Examples

The examples use a 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 ORDER BY id) 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 ORDER BY 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 ORDER BY id) AS `Subjects`
FROM student_score
GROUP BY name
ORDER 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.