MySQL JSON_OBJECTAGG() Function

The MySQL JSON_OBJECTAGG() function aggregates key-value pairs with the first parameter as the key and the second parameter as the value into a JSON object.

JSON_OBJECTAGG() Syntax

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

JSON_OBJECTAGG(key_expr, value_expr)

Typically, we use the JSON_OBJECTAGG() function like:

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

Parameters

key_expr

Required. Its value is used as the key in the key-value pair in the result object. It can be a column name or an expression.

value_expr

Optional. Its value is used as the value in the key-value pair in the result object. It can be a column name or an expression.

Return value

The MySQL JSON_OBJECTAGG() function returns a JSON object where the key specified by key_expr and the value specified by the value_expr.

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 returns NULL if the result set has no rows.

JSON_OBJECTAGG() 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_OBJECTAGG() function to get all scores for everyone as follows:

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

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

Here, the values ​​of the Scores columns are JSON objects. For Tim, since he only took one exam, there is only one key-value pair in the JSON object. For Tom, since he took two exams, there are two key-value pairs in the JSON object.