In MariaDB, JSON_OBJECTAGG() is a built-in function that returns a JSON object containing key-value pairs, based on its two arguments.

Syntax

The syntax goes like this:

JSON_OBJECTAGG(key, value)

The function accepts two expressions that evaluate to a single value, or two column names, as arguments. The first argument is the key, and the second is its value.

Example

Here’s a simple example to demonstrate:

SELECT JSON_OBJECTAGG("name", "Homer");

Result:

+---------------------------------+
| JSON_OBJECTAGG("name", "Homer") |
+---------------------------------+
| {"name":"Homer"}                |
+---------------------------------+

Although this example demonstrates how the function works, the real benefit comes in when working with columns or other expressions.

Below are examples that use database columns for the arguments.

A Database Example

Suppose we query a table:

SELECT 
    PetName,
    DOB
FROM Pets;

And get the following result set:

+---------+------------+
| PetName | DOB        |
+---------+------------+
| Fluffy  | 2020-11-20 |
| Fetch   | 2019-08-16 |
| Scratch | 2018-10-01 |
| Wag     | 2020-03-15 |
| Tweet   | 2020-11-28 |
| Fluffy  | 2020-09-17 |
| Bark    | NULL       |
| Meow    | NULL       |
+---------+------------+

Let’s now run a query that passes each column to the JSON_OBJECTAGG() function, so that the results are returned as a JSON object:

SELECT JSON_OBJECTAGG(PetName, DOB)
FROM Pets
WHERE DOB < '2020-04-01';

Result:

+--------------------------------------------------------------------+
| JSON_OBJECTAGG(PetName, DOB)                                       |
+--------------------------------------------------------------------+
| {"Fetch":"2019-08-16", "Scratch":"2018-10-01", "Wag":"2020-03-15"} |
+--------------------------------------------------------------------+

All we did was pass the column names to the JSON_OBJECTAGG() function.

We also used a WHERE clause to narrow the results down a bit.

Grouped Results

We can use the SQL GROUP BY clause to produce JSON objects based on a grouping of another column.

Suppose we add a column to our original query:

SELECT 
    PetTypeId,
    PetName,
    DOB
FROM Pets;

Result:

+-----------+---------+------------+
| PetTypeId | PetName | DOB        |
+-----------+---------+------------+
|         2 | Fluffy  | 2020-11-20 |
|         3 | Fetch   | 2019-08-16 |
|         2 | Scratch | 2018-10-01 |
|         3 | Wag     | 2020-03-15 |
|         1 | Tweet   | 2020-11-28 |
|         3 | Fluffy  | 2020-09-17 |
|         3 | Bark    | NULL       |
|         2 | Meow    | NULL       |
+-----------+---------+------------+

Now we have a PetTypeId column as well as the PetName and DOB columns. This matches a pet type to each pet.

Here’s an example of using the GROUP BY clause to group our results by the PetTypeId column while using the JSON_OBJECTAGG() function:

SELECT 
    PetTypeId,
    JSON_OBJECTAGG(PetName, DOB)
FROM Pets
GROUP BY PetTypeId;

Result:

+-----------+--------------------------------------------------------------------------------+
| PetTypeId | JSON_OBJECTAGG(PetName, DOB)                                                   |
+-----------+--------------------------------------------------------------------------------+
|         1 | {"Tweet":"2020-11-28"}                                                         |
|         2 | {"Fluffy":"2020-11-20", "Scratch":"2018-10-01", "Meow":null}                   |
|         3 | {"Fetch":"2019-08-16", "Wag":"2020-03-15", "Fluffy":"2020-09-17", "Bark":null} |
+-----------+--------------------------------------------------------------------------------+

This allowed us to create a separate JSON object for each pet type.

The following query uses an INNER JOIN on another table to return the actual pet type, not just the ID.

SELECT 
    pt.PetType,
    p.PetName,
    p.DOB
FROM Pets p 
INNER JOIN PetTypes pt 
ON pt.PetTypeId = p.PetTypeId
ORDER BY PetType;

Result:

+---------+---------+------------+
| PetType | PetName | DOB        |
+---------+---------+------------+
| Bird    | Tweet   | 2020-11-28 |
| Cat     | Scratch | 2018-10-01 |
| Cat     | Fluffy  | 2020-11-20 |
| Cat     | Meow    | NULL       |
| Dog     | Wag     | 2020-03-15 |
| Dog     | Fetch   | 2019-08-16 |
| Dog     | Bark    | NULL       |
| Dog     | Fluffy  | 2020-09-17 |
+---------+---------+------------+

We can see that the actual pet type is now listed in the first column, instead of just the pet type ID.

Now let’s use the JSON_OBJECTAGG() function:

SELECT 
    pt.PetType,
    JSON_OBJECTAGG(p.PetName, p.DOB)
FROM Pets p 
INNER JOIN PetTypes pt 
ON pt.PetTypeId = p.PetTypeId
GROUP BY pt.PetType;

Result:

+---------+--------------------------------------------------------------------------------+
| PetType | JSON_OBJECTAGG(p.PetName, p.DOB)                                               |
+---------+--------------------------------------------------------------------------------+
| Bird    | {"Tweet":"2020-11-28"}                                                         |
| Cat     | {"Scratch":"2018-10-01", "Fluffy":"2020-11-20", "Meow":null}                   |
| Dog     | {"Wag":"2020-03-15", "Fetch":"2019-08-16", "Bark":null, "Fluffy":"2020-09-17"} |
+---------+--------------------------------------------------------------------------------+