MySQL 8.0 Labs: JSON aggregation functions

In MySQL 5.7 we introduced JSON functionality into the MySQL Server. This work included the introduction of a JSON data type, virtual columns and a set of approximately 20 SQL functions that allow you to manipulate and search JSON data on the server side.

The JSON functionality has been well received, and in MySQL 8.0 we have plans to improve it in a number of ways. This post outlines improvements to the SQL functions with the addition of aggregate functions.


Starting with MySQL 8.0 (lab release)* two  new aggregation functions were added and can be used to combine data into JSON arrays/objects:


*(To download MySQL 8.0-labs release go to this link and choose “MySQL Server 8.0.0 Optimizer)

Now for the less impatient:

Let’s think about this scenario: you have a database which contains both structured and semi-structured data and you’ve decided to adopt the EAV model (Entity–Attribute–Value). The tables will look more or less like this:

You have a product table which contains the common attributes:

Then you have the attribute table containing all the non-common attributes that a product might have:

With some possible entries:

And finally the value table which combines the product key, the attribute key with the actual value.

Now let’s insert a few products and their attributes:

If you need to select complete products that combines all the attribute keys and values as JSON object, though combining the structured data (in product) and the semi-structured data (in attribute and in value):  you can use the JSON_OBJECTAGG aggregation function:

To select an array containing all the possible attribute keys for each product JSON_ARRAYAGG  can be used:

The functions can also be used to help you migrate to a new schema having the semi-structured data stored in JSON columns:

Can this be achieved in 5.7 too? Yes, but it’s more complicated.


The addition of aggregate functions makes it easier to aggregate data into JSON. Please try out these new features, and let us know your feedback!

Thank you for using MySQL.

4 thoughts on “MySQL 8.0 Labs: JSON aggregation functions

  1. Is the reverse function in the works, a sort of JSON_SPLIT table valued function? So I could do something like:

    SET @j = ‘[1, 2, 3]’;

    JSON_SPLIT(JSON_EXTRACT(@j, ‘$[ * ]’), ‘$’)

    1. Hi Chris! The SQL standard defines what you just described as a JSON_TABLE function. We are looking to add something similar.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.