MariaDB JSON_ARRAYAGG() Explained

In MariaDB, JSON_ARRAYAGG() is a built-in function that returns a JSON array containing an element for each value in a given set of JSON or SQL values.

The function acts on a column or an expression that evaluates to a single value. It enables you to aggregate a result set as a single JSON array. Each row of the result set ends up as a single element in the array.

Syntax

The syntax goes like this:

JSON_ARRAYAGG([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [LIMIT {[offset,] row_count | row_count OFFSET offset}])

Example

Suppose we query a table:

SELECT PetName
FROM Pets;

And get the following result set:

+---------+
| PetName |
+---------+
| Fluffy  |
| Fetch   |
| Scratch |
| Wag     |
| Tweet   |
| Fluffy  |
| Bark    |
| Meow    |
+---------+

The result is one column, and each row contains a different pet name.

Let’s say we wanted all the pets to be listed in a JSON array (so that each pet name was its own array element).

We can use the JSON_ARRAYAGG() function to do just that:

SELECT JSON_ARRAYAGG(PetName)
FROM Pets;

Result:

+-------------------------------------------------------------------+
| JSON_ARRAYAGG(PetName)                                            |
+-------------------------------------------------------------------+
| ["Fluffy","Fetch","Scratch","Wag","Tweet","Fluffy","Bark","Meow"] |
+-------------------------------------------------------------------+

All we did was pass the column name to the JSON_ARRAYAGG() function.

Distinct Results

We can add the DISTINCT clause to remove duplicate values from the array:

SELECT JSON_ARRAYAGG(DISTINCT PetName)
FROM Pets;

Result:

+----------------------------------------------------------+
| JSON_ARRAYAGG(DISTINCT PetName)                          |
+----------------------------------------------------------+
| ["Bark","Fetch","Fluffy","Meow","Scratch","Tweet","Wag"] |
+----------------------------------------------------------+

Notice that Fluffy was only included once here, whereas Fluffy was included twice in the previous example (because there are two pets called Fluffy).

Order the Results

We can use the ORDER BY clause to specify an order for the array elements:

SELECT JSON_ARRAYAGG(PetName ORDER BY PetName DESC)
FROM Pets;

Result:

+-------------------------------------------------------------------+
| JSON_ARRAYAGG(PetName ORDER BY PetName DESC)                      |
+-------------------------------------------------------------------+
| ["Wag","Tweet","Scratch","Meow","Fluffy","Fluffy","Fetch","Bark"] |
+-------------------------------------------------------------------+

Limit the Results

We can use the LIMIT clause to specify an order for the array elements:

SELECT JSON_ARRAYAGG(PetName LIMIT 3)
FROM Pets;

Result:

+--------------------------------+
| JSON_ARRAYAGG(PetName LIMIT 3) |
+--------------------------------+
| ["Fluffy","Fetch","Scratch"]   |
+--------------------------------+

We can also use an offset for the LIMIT clause:

SELECT JSON_ARRAYAGG(PetName LIMIT 3 OFFSET 2)
FROM Pets;

Result:

+-----------------------------------------+
| JSON_ARRAYAGG(PetName LIMIT 3 OFFSET 2) |
+-----------------------------------------+
| ["Scratch","Wag","Tweet"]               |
+-----------------------------------------+

Alternatively, we can omit the LIMIT and OFFSET keywords, and switch the numbers around (and separate them with a comma) to achieve the same result:

SELECT JSON_ARRAYAGG(PetName LIMIT 2, 3)
FROM Pets;

Result:

+-----------------------------------+
| JSON_ARRAYAGG(PetName LIMIT 2, 3) |
+-----------------------------------+
| ["Scratch","Wag","Tweet"]         |
+-----------------------------------+

Grouped Results

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

Suppose we add a column to our original query:

SELECT 
    PetTypeId,
    PetName
FROM Pets;

Result:

+-----------+---------+
| PetTypeId | PetName |
+-----------+---------+
|         2 | Fluffy  |
|         3 | Fetch   |
|         2 | Scratch |
|         3 | Wag     |
|         1 | Tweet   |
|         3 | Fluffy  |
|         3 | Bark    |
|         2 | Meow    |
+-----------+---------+

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

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

SELECT 
    PetTypeId,
    JSON_ARRAYAGG(PetName)
FROM Pets
GROUP BY PetTypeId;

Result:

+-----------+---------------------------------+
| PetTypeId | JSON_ARRAYAGG(PetName)          |
+-----------+---------------------------------+
|         1 | ["Tweet"]                       |
|         2 | ["Fluffy","Scratch","Meow"]     |
|         3 | ["Fetch","Wag","Fluffy","Bark"] |
+-----------+---------------------------------+

This allowed us to create a separate array 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
FROM Pets p 
INNER JOIN PetTypes pt 
ON pt.PetTypeId = p.PetTypeId
ORDER BY PetType;

Result:

+---------+---------+
| PetType | PetName |
+---------+---------+
| Bird    | Tweet   |
| Cat     | Scratch |
| Cat     | Fluffy  |
| Cat     | Meow    |
| Dog     | Wag     |
| Dog     | Fetch   |
| Dog     | Bark    |
| Dog     | Fluffy  |
+---------+---------+

We can see that each pet type is listed in the first column and the pet name is listed in the second column.

Now let’s use the JSON_ARRAYAGG() function:

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

Result:

+---------+--------------------------+
| PetType | JSON_ARRAYAGG(p.PetName) |
+---------+--------------------------+
| Bird    | Tweet                    |
| Cat     | Scratch,Fluffy,Meow      |
| Dog     | Wag,Fetch,Bark,Fluffy    |
+---------+--------------------------+