{"id":18225,"date":"2021-07-03T09:23:22","date_gmt":"2021-07-02T23:23:22","guid":{"rendered":"https:\/\/database.guide\/?p=18225"},"modified":"2021-07-03T09:23:22","modified_gmt":"2021-07-02T23:23:22","slug":"mariadb-json_objectagg-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/mariadb-json_objectagg-explained\/","title":{"rendered":"MariaDB JSON_OBJECTAGG() Explained"},"content":{"rendered":"\n

In MariaDB, JSON_OBJECTAGG()<\/code> is a built-in function that returns a JSON object containing key-value pairs, based on its two arguments. <\/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_OBJECTAGG(key, value)<\/code><\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s a simple example to demonstrate:<\/p>\n\n\n\n
SELECT JSON_OBJECTAGG(\"name\", \"Homer\");<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+---------------------------------+\n| JSON_OBJECTAGG(\"name\", \"Homer\") |\n+---------------------------------+\n| {\"name\":\"Homer\"}                |\n+---------------------------------+<\/pre>\n\n\n\n
Although this example demonstrates how the function works, the real benefit comes in when working with columns or other expressions.<\/p>\n\n\n\n
Below are examples that use database columns for the arguments.<\/p>\n\n\n\n
A Database Example<\/h2>\n\n\n\n
Suppose we query a table:<\/p>\n\n\n\n
SELECT \n    PetName,\n    DOB\nFROM Pets;<\/code><\/pre>\n\n\n\n
And get the following result set:<\/p>\n\n\n\n
+---------+------------+\n| PetName | DOB        |\n+---------+------------+\n| Fluffy  | 2020-11-20 |\n| Fetch   | 2019-08-16 |\n| Scratch | 2018-10-01 |\n| Wag     | 2020-03-15 |\n| Tweet   | 2020-11-28 |\n| Fluffy  | 2020-09-17 |\n| Bark    | NULL       |\n| Meow    | NULL       |\n+---------+------------+<\/pre>\n\n\n\n
Let’s now run a query that passes each column to the JSON_OBJECTAGG()<\/code> function, so that the results are returned as a JSON object:<\/p>\n\n\n\n
SELECT JSON_OBJECTAGG(PetName, DOB)\nFROM Pets\nWHERE DOB < '2020-04-01';<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------------------------------------------------------------------+\n| JSON_OBJECTAGG(PetName, DOB)                                       |\n+--------------------------------------------------------------------+\n| {\"Fetch\":\"2019-08-16\", \"Scratch\":\"2018-10-01\", \"Wag\":\"2020-03-15\"} |\n+--------------------------------------------------------------------+<\/pre>\n\n\n\n
All we did was pass the column names to the JSON_OBJECTAGG()<\/code> function. <\/p>\n\n\n\n
We also used a WHERE<\/code> clause to narrow the results down a bit.<\/p>\n\n\n\n
Grouped Results<\/h2>\n\n\n\n
We can use the SQL GROUP BY<\/a><\/code> clause to produce JSON objects 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,\n    DOB\nFROM Pets;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------+---------+------------+\n| PetTypeId | PetName | DOB        |\n+-----------+---------+------------+\n|         2 | Fluffy  | 2020-11-20 |\n|         3 | Fetch   | 2019-08-16 |\n|         2 | Scratch | 2018-10-01 |\n|         3 | Wag     | 2020-03-15 |\n|         1 | Tweet   | 2020-11-28 |\n|         3 | Fluffy  | 2020-09-17 |\n|         3 | Bark    | NULL       |\n|         2 | Meow    | NULL       |\n+-----------+---------+------------+<\/pre>\n\n\n\n
Now we have a PetTypeId<\/code> column as well as the PetName<\/code> and DOB<\/code> columns. This matches a pet type to each pet.<\/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_OBJECTAGG()<\/code> function:<\/p>\n\n\n\n
SELECT \n    PetTypeId,\n    JSON_OBJECTAGG(PetName, DOB)\nFROM Pets\nGROUP BY PetTypeId;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------+--------------------------------------------------------------------------------+\n| PetTypeId | JSON_OBJECTAGG(PetName, DOB)                                                   |\n+-----------+--------------------------------------------------------------------------------+\n|         1 | {\"Tweet\":\"2020-11-28\"}                                                         |\n|         2 | {\"Fluffy\":\"2020-11-20\", \"Scratch\":\"2018-10-01\", \"Meow\":null}                   |\n|         3 | {\"Fetch\":\"2019-08-16\", \"Wag\":\"2020-03-15\", \"Fluffy\":\"2020-09-17\", \"Bark\":null} |\n+-----------+--------------------------------------------------------------------------------+<\/pre>\n\n\n\n
This allowed us to create a separate JSON object 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,\n    p.DOB\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 | DOB        |\n+---------+---------+------------+\n| Bird    | Tweet   | 2020-11-28 |\n| Cat     | Scratch | 2018-10-01 |\n| Cat     | Fluffy  | 2020-11-20 |\n| Cat     | Meow    | NULL       |\n| Dog     | Wag     | 2020-03-15 |\n| Dog     | Fetch   | 2019-08-16 |\n| Dog     | Bark    | NULL       |\n| Dog     | Fluffy  | 2020-09-17 |\n+---------+---------+------------+<\/pre>\n\n\n\n
We can see that the actual pet type is now listed in the first column, instead of just the pet type ID. <\/p>\n\n\n\n
Now let’s use the JSON_OBJECTAGG()<\/code> function:<\/p>\n\n\n\n
SELECT \n    pt.PetType,\n    JSON_OBJECTAGG(p.PetName, p.DOB)\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_OBJECTAGG(p.PetName, p.DOB)                                               |\n+---------+--------------------------------------------------------------------------------+\n| Bird    | {\"Tweet\":\"2020-11-28\"}                                                         |\n| Cat     | {\"Scratch\":\"2018-10-01\", \"Fluffy\":\"2020-11-20\", \"Meow\":null}                   |\n| Dog     | {\"Wag\":\"2020-03-15\", \"Fetch\":\"2019-08-16\", \"Bark\":null, \"Fluffy\":\"2020-09-17\"} |\n+---------+--------------------------------------------------------------------------------+<\/pre>\n","protected":false},"excerpt":{"rendered":"
In MariaDB, JSON_OBJECTAGG() is a built-in function that returns a JSON object containing key-value pairs, based on its two arguments.<\/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":[93,80,20],"class_list":["post-18225","post","type-post","status-publish","format-standard","hentry","category-mariadb","tag-functions","tag-json","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18225","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=18225"}],"version-history":[{"count":6,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18225\/revisions"}],"predecessor-version":[{"id":18236,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18225\/revisions\/18236"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=18225"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=18225"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=18225"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}