MySQL JSON_OBJECTAGG() Function (original) (raw)

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.

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:

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

Was this tutorial helpful?