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.

TL;DR

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

  • JSON_ARRAYAGG()
  • JSON_OBJECTAGG()

*(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.

12 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]’;

    SELECT GROUP_CONCAT(val)
    FROM
    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.

      1. Really? What am I doing wrong?

        mysql> SELECT JSON_ARRAYAGG(DISTINCT d) FROM t;
        ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘DISTINCT d) FROM t’ at line 1

        This is 8.0.2-dmr

        mysql> SELECT JSON_ARRAYAGG(d) FROM t;
        +————————–+
        | JSON_ARRAYAGG(d) |
        +————————–+
        | [10, 10, 10, 20, 20, 30] |
        +————————–+

        I’d expect JSON_ARRAY(DISTINCT d) to produce [10, 20, 30]

        1. Ok. I thought you were refering to SELECT DISTINCT JSON_*AGG. At the moment JSON_ARRAYAGG does not support DISTINCT option but this is something that we can consider implementing. JSON_OBJECTAGG does not need it because MySQL’s JSON implementation will, by default, skips the key-value pair insertion if the key is duplicate. Thanks for the suggestion! Please consider filling in a feature request at https://bugs.mysql.com/

Leave a Reply

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

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