MySQL JSON_OBJECTAGG() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_OBJECTAGG() function to aggregate key-value pairs from columns into a JSON object.

Introduction to MySQL JSON_OBJECTAGG() function

The JSON_OBJECTAGG() function aggregates key-value pairs from columns into a JSON object. Here’s the syntax of the JSON_OBJECTAGG() function:

JSON_OBJECTAGG(key, value)Code language: SQL (Structured Query Language) (sql)

The JSON_OBJECTAGG() function takes two column names as arguments: The first being used as the key and the second as the value.

The JSON_OBJECTAGG() function returns a JSON object containing key-value pairs. It returns NULL if the result has no rows or in case of an error.

The error occurs when any key name is NULL or the number of arguments is not equal to 2.

In practice, you often use the JSON_OBJECTAGG() function with the GROUP BY clause to aggregate data into JSON objects for each group produced by the GROUP BY clause.

MySQL JSON_OBJECTAGG() function example

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.

First, create a table called properties:

CREATE TABLE properties (
    id INT,
    attribute VARCHAR(255) NOT NULL,
    value VARCHAR(255)
);Code language: SQL (Structured Query Language) (sql)

The properties table has three columns:

• id: This is the column that stores the id of records of another table e..g, products.
• attribute: This column stores the attribute name.
• value: This column stores the attribute value.

Second, insert some sample data into the properties table:

INSERT INTO properties(id, attribute, value) 
VALUES
    (1, 'color', 'red'),
    (1, 'size', 'medium'),
    (1, 'shape', 'circle'),
    (2, 'color', 'blue'),
    (2, 'size', 'large'),
    (3, 'color', 'green'),
    (3, 'shape', 'square');Code language: SQL (Structured Query Language) (sql)

Third, query data from the properties table:

SELECT * FROM properties;Code language: SQL (Structured Query Language) (sql)

Output:

+------+-----------+--------+
| id   | attribute | value  |
+------+-----------+--------+
|    1 | color     | red    |
|    1 | size      | medium |
|    1 | shape     | circle |
|    2 | color     | blue   |
|    2 | size      | large  |
|    3 | color     | green  |
|    3 | shape     | square |
+------+-----------+--------+
7 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Finally, aggregate data by the values in the id column using the JSON_OBJECTAGG() function:

SELECT 
  id, 
  JSON_OBJECTAGG(attribute, value) AS attribute_value 
FROM 
  properties 
GROUP BY 
  id;Code language: SQL (Structured Query Language) (sql)

Output:

+------+-------------------------------------------------------+
| id   | attribute_value                                       |
+------+-------------------------------------------------------+
|    1 | {"size": "medium", "color": "red", "shape": "circle"} |
|    2 | {"size": "large", "color": "blue"}                    |
|    3 | {"color": "green", "shape": "square"}                 |
+------+-------------------------------------------------------+
3 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the MySQL JSON_OBJECTAGG() function to aggregate key-value pairs from columns into JSON objects.