{"id":18216,"date":"2021-07-02T06:27:48","date_gmt":"2021-07-01T20:27:48","guid":{"rendered":"https:\/\/database.guide\/?p=18216"},"modified":"2024-04-28T11:48:43","modified_gmt":"2024-04-28T01:48:43","slug":"mariadb-json_arrayagg-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/mariadb-json_arrayagg-explained\/","title":{"rendered":"MariaDB JSON_ARRAYAGG() Explained"},"content":{"rendered":"\n

In MariaDB, JSON_ARRAYAGG()<\/code> 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. <\/p>\n\n\n\n

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.<\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

The syntax goes like this:<\/p>\n\n\n\n

JSON_ARRAYAGG([DISTINCT] expr [,expr ...]\n             [ORDER BY {unsigned_integer | col_name | expr}\n                 [ASC | DESC] [,col_name ...]]\n             [LIMIT {[offset,] row_count | row_count OFFSET offset}])<\/code><\/pre>\n\n\n\n
Example<\/h2>\n\n\n\n
Suppose we query a table:<\/p>\n\n\n\n
SELECT PetName\nFROM Pets;<\/code><\/pre>\n\n\n\n
And get the following result set:<\/p>\n\n\n\n
+---------+\n| PetName |\n+---------+\n| Fluffy  |\n| Fetch   |\n| Scratch |\n| Wag     |\n| Tweet   |\n| Fluffy  |\n| Bark    |\n| Meow    |\n+---------+<\/pre>\n\n\n\n
The result is one column, and each row contains a different pet name.<\/p>\n\n\n\n
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).<\/p>\n\n\n\n
We can use the JSON_ARRAYAGG()<\/code> function to do just that:<\/p>\n\n\n\n
SELECT JSON_ARRAYAGG(PetName)\nFROM Pets;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------------------------------------------------------------+\n| JSON_ARRAYAGG(PetName)                                            |\n+-------------------------------------------------------------------+\n| [\"Fluffy\",\"Fetch\",\"Scratch\",\"Wag\",\"Tweet\",\"Fluffy\",\"Bark\",\"Meow\"] |\n+-------------------------------------------------------------------+<\/pre>\n\n\n\n
All we did was pass the column name to the JSON_ARRAYAGG()<\/code> function. <\/p>\n\n\n\n
Distinct Results<\/h2>\n\n\n\n
We can add the DISTINCT<\/code> clause to remove duplicate values from the array: <\/p>\n\n\n\n
SELECT JSON_ARRAYAGG(DISTINCT PetName)\nFROM Pets;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+----------------------------------------------------------+\n| JSON_ARRAYAGG(DISTINCT PetName)                          |\n+----------------------------------------------------------+\n| [\"Bark\",\"Fetch\",\"Fluffy\",\"Meow\",\"Scratch\",\"Tweet\",\"Wag\"] |\n+----------------------------------------------------------+<\/pre>\n\n\n\n
Notice that Fluffy<\/code> was only included once here, whereas Fluffy<\/code> was included twice in the previous example (because there are two pets called Fluffy<\/code>).<\/p>\n\n\n\n
Order the Results<\/h2>\n\n\n\n
We can use the ORDER BY<\/code> clause to specify an order for the array elements: <\/p>\n\n\n\n
SELECT JSON_ARRAYAGG(PetName ORDER BY PetName DESC)\nFROM Pets;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------------------------------------------------------------+\n| JSON_ARRAYAGG(PetName ORDER BY PetName DESC)                      |\n+-------------------------------------------------------------------+\n| [\"Wag\",\"Tweet\",\"Scratch\",\"Meow\",\"Fluffy\",\"Fluffy\",\"Fetch\",\"Bark\"] |\n+-------------------------------------------------------------------+<\/pre>\n\n\n\n
Limit the Results<\/h2>\n\n\n\n
We can use the LIMIT<\/code> clause to specify an order for the array elements: <\/p>\n\n\n\n
SELECT JSON_ARRAYAGG(PetName LIMIT 3)\nFROM Pets;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------------------------------+\n| JSON_ARRAYAGG(PetName LIMIT 3) |\n+--------------------------------+\n| [\"Fluffy\",\"Fetch\",\"Scratch\"]   |\n+--------------------------------+<\/pre>\n\n\n\n
We can also use an offset for the LIMIT<\/code> clause:<\/p>\n\n\n\n
SELECT JSON_ARRAYAGG(PetName LIMIT 3 OFFSET 2)\nFROM Pets;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------------------------------------+\n| JSON_ARRAYAGG(PetName LIMIT 3 OFFSET 2) |\n+-----------------------------------------+\n| [\"Scratch\",\"Wag\",\"Tweet\"]               |\n+-----------------------------------------+<\/pre>\n\n\n\n
Alternatively, we can omit the LIMIT<\/code> and OFFSET<\/code> keywords, and switch the numbers around (and separate them with a comma) to achieve the same result:<\/p>\n\n\n\n
SELECT JSON_ARRAYAGG(PetName LIMIT 2, 3)\nFROM Pets;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------------------------------+\n| JSON_ARRAYAGG(PetName LIMIT 2, 3) |\n+-----------------------------------+\n| [\"Scratch\",\"Wag\",\"Tweet\"]         |\n+-----------------------------------+<\/pre>\n\n\n\n
Grouped Results<\/h2>\n\n\n\n
We can use the SQL GROUP BY<\/a><\/code> clause to produce arrays based on a grouping of another column.<\/p>\n\n\n\n
Suppose we add a column to our original query:<\/p>\n\n\n\n
SELECT \n    PetTypeId,\n    PetName\nFROM Pets;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------+---------+\n| PetTypeId | PetName |\n+-----------+---------+\n|         2 | Fluffy  |\n|         3 | Fetch   |\n|         2 | Scratch |\n|         3 | Wag     |\n|         1 | Tweet   |\n|         3 | Fluffy  |\n|         3 | Bark    |\n|         2 | Meow    |\n+-----------+---------+<\/pre>\n\n\n\n
Now we have a PetTypeId<\/code> column as well as the PetName<\/code> column. This matches a pet type to each name.<\/p>\n\n\n\n
Here’s an example of using the GROUP BY<\/code> clause to group our results by the PetTypeId<\/code> column while using the JSON_ARRAYAGG()<\/code> function:<\/p>\n\n\n\n
SELECT \n    PetTypeId,\n    JSON_ARRAYAGG(PetName)\nFROM Pets\nGROUP BY PetTypeId;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------+---------------------------------+\n| PetTypeId | JSON_ARRAYAGG(PetName)          |\n+-----------+---------------------------------+\n|         1 | [\"Tweet\"]                       |\n|         2 | [\"Fluffy\",\"Scratch\",\"Meow\"]     |\n|         3 | [\"Fetch\",\"Wag\",\"Fluffy\",\"Bark\"] |\n+-----------+---------------------------------+<\/pre>\n\n\n\n
This allowed us to create a separate array for each pet type.<\/p>\n\n\n\n
The following query uses an INNER JOIN<\/a><\/code> on another table to return the actual pet type, not just the ID.<\/p>\n\n\n\n
SELECT \n    pt.PetType,\n    p.PetName\nFROM Pets p \nINNER JOIN PetTypes pt \nON pt.PetTypeId = p.PetTypeId\nORDER BY PetType;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+---------+---------+\n| PetType | PetName |\n+---------+---------+\n| Bird    | Tweet   |\n| Cat     | Scratch |\n| Cat     | Fluffy  |\n| Cat     | Meow    |\n| Dog     | Wag     |\n| Dog     | Fetch   |\n| Dog     | Bark    |\n| Dog     | Fluffy  |\n+---------+---------+<\/pre>\n\n\n\n
We can see that each pet type is listed in the first column and the pet name is listed in the second column. <\/p>\n\n\n\n
Now let’s use the JSON_ARRAYAGG()<\/code> function:<\/p>\n\n\n\n
SELECT \n    pt.PetType,\n    JSON_ARRAYAGG(p.PetName)\nFROM Pets p \nINNER JOIN PetTypes pt \nON pt.PetTypeId = p.PetTypeId\nGROUP BY pt.PetType;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+---------+--------------------------+\n| PetType | JSON_ARRAYAGG(p.PetName) |\n+---------+--------------------------+\n| Bird    | Tweet                    |\n| Cat     | Scratch,Fluffy,Meow      |\n| Dog     | Wag,Fetch,Bark,Fluffy    |\n+---------+--------------------------+<\/pre>\n","protected":false},"excerpt":{"rendered":"
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. … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[117],"tags":[169,93,80,20],"class_list":["post-18216","post","type-post","status-publish","format-standard","hentry","category-mariadb","tag-arrays","tag-functions","tag-json","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18216","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=18216"}],"version-history":[{"count":9,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18216\/revisions"}],"predecessor-version":[{"id":18233,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18216\/revisions\/18233"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=18216"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=18216"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=18216"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}