{"id":12081,"date":"2023-11-04T18:54:56","date_gmt":"2023-11-05T01:54:56","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=12081"},"modified":"2023-11-04T18:59:13","modified_gmt":"2023-11-05T01:59:13","slug":"mysql-json_objectagg","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-json\/mysql-json_objectagg\/","title":{"rendered":"MySQL JSON_OBJECTAGG() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL JSON_OBJECTAGG()<\/code> function to aggregate key-value pairs from columns into a JSON object.<\/p>\n\n\n\n

Introduction to MySQL JSON_OBJECTAGG() function<\/h2>\n\n\n\n

The JSON_OBJECTAGG()<\/code> function aggregates key-value pairs from columns into a JSON object. Here’s the syntax of the JSON_OBJECTAGG()<\/code> function:<\/p>\n\n\n

JSON_OBJECTAGG(key, value)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The JSON_OBJECTAGG()<\/code> function takes two column names as arguments: The first being used as the key and the second as the value.<\/p>\n\n\n\n
The JSON_OBJECTAGG()<\/code> function returns a JSON object containing key-value pairs. It returns NULL<\/code> if the result has no rows or in case of an error.<\/p>\n\n\n\n
The error occurs when any key name is NULL<\/code> or the number of arguments is not equal to 2.<\/p>\n\n\n\n
In practice, you often use the JSON_OBJECTAGG()<\/code> function with the GROUP BY<\/a> clause to aggregate data into JSON objects for each group produced by the GROUP BY clause.<\/p>\n\n\n\n
MySQL JSON_OBJECTAGG() function example<\/h2>\n\n\n\n
In the following example, we’ll first create a table to store the custom fields of an entity in the database and then use the JSON_OBJECTAGG() function to aggregate key-value pairs into a JSON object.<\/p>\n\n\n\n
First, create a table<\/a> called properties<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> properties (\n    id<\/span> INT<\/span>,\n    attribute<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    value<\/span> VARCHAR<\/span>(255<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The properties table has three columns:<\/p>\n\n\n\n