MariaDB JSON_OBJECTAGG() Function

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.

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: Optional. Its value acts as the value of the key-value pair in the result object. It can be a column name or an expression.

Return value

The MariaDB JSON_OBJECTAGG() function returns a JSON object in which key_expr is the key and value_expr is the value of the key-value pair.

If there are duplicate keys, only the last key is kept as a key-value pair, and other duplicate key-value pairs are discarded.

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

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 obviously doesn’t have much useful value. By grouping you can get more precise statistics.

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