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 | +---------+--------------------------+