MariaDB JSON_OBJECTAGG(): Syntax, GROUP BY and Examples

MariaDB JSON_OBJECTAGG(key, value) aggregates rows into one JSON object. It is available from MariaDB 10.5. Each input row contributes one key-value pair. See the official MariaDB JSON_OBJECTAGG reference.

MariaDB `JSON_OBJECTAGG()` Syntax

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

JSON_OBJECTAGG(key_expr, value_expr)

We generally use the JSON_OBJECTAGG() function in a SELECT statement like this:

SELECT JSON_OBJECTAGG(key_expr, value_expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

Parameters

key_expr: Required. Its value acts as the key of the key-value pair in the result object. It can be a column name or an expression.
value_expr: Required. Its value becomes the value paired with key_expr. It can be a column name or an expression.

Return value

The function returns a JSON object containing the key-value pairs from the input rows. If several rows have the same key, MariaDB preserves the duplicate key names in the JSON text rather than replacing earlier pairs. With duplicate keys, MariaDB documents that functions such as JSON_EXTRACT() can access only the first matching pair; use unique keys when a single unambiguous value is required. See MariaDB’s JSON type documentation.

The maximum returned length is controlled by the group_concat_max_len system variable. The function returns NULL if the result has no rows or an error occurs. JSON_OBJECTAGG() cannot be used as a window function.

MariaDB `JSON_OBJECTAGG()` Examples

We’ll demonstrate a example 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 create a table to store the scores 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’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 |
+----+------+---------+-------+

Basic example

To get a JSON object containing all the subjects and scores, use the following statement:

SELECT
    JSON_OBJECTAGG(subject, score) AS `Scores`
FROM student_score;

Output:

+-----------------------------------------+
| Scores                                  |
+-----------------------------------------+
| {"Math":80, "English":90, "English":98} |
+-----------------------------------------+

This result contains two English keys because Tom has two rows for that subject. When consumers need one value per key, remove or aggregate duplicate source rows before calling JSON_OBJECTAGG().

Group

By grouping, we can use the JSON_OBJECTAGG() function to get everyone’s scores, as follows:

SELECT
    name AS `Name`,
    JSON_OBJECTAGG(subject, score) AS `Scores`
FROM student_score
GROUP BY name;

Output:

+------+-----------------------------+
| Name | Scores                      |
+------+-----------------------------+
| Tim  | {"English": 98}             |
| Tom  | {"Math": 80, "English": 90} |
+------+-----------------------------+

In this example, we group all rows by name. The values of the Scores columns are JSON objects.

For Tim, he took one exam, there is {"English": 98}.

For Tom, he took two exams, there are two key-value pairs in {"Math": 80, "English": 90}.

Conclusion

In MariaDB, JSON_OBJECTAGG() is a built-in function that aggregates key-value pairs with the first parameter as the key and the second parameter as the value into a JSON object.