JSON specific window functions in MySQL 8.0

In 8.0, we introduced window functions in the MySQL server. A detailed explanation of how window functions can be used and what is supported in 8.0 is explained here.

Now, let’s see what is new in window functions in the latest version 8.0. Starting in 8.0.14, JSON aggregate functions can be used as window functions, i.e. they can be specified with an OVER() clause. An explanation of JSON grouped aggregate functions (i.e. without the OVER() clause) can be found here.

JSON_ARRAYAGG() as window function

While a grouped aggregate returns a JSON_ARRAY for each group, the corresponding aggregate used as a window function would return a JSON_ARRAY for every row. The JSON_ARRAY constructed for each row will differ or be the same based on the frame, partition and order specification for the window. Let us look at an example.

We will retrieve the names of all those employees who have been hired on or before a particular date in a department using the JSON_ARRAYAGG window function. To achieve this we use the window function’s PARTITION BY and ORDER BY clause.

 

JSON_OBJECTAGG as window function

Similar to the case of JSON_ARRAYAGG, when JSON_OBJECTAGG  is used as a window function, it returns a JSON object having key-value pairs for every row. The result for each row depends on the frame, partition and order specification for that window.

For the same table as used above,  if we want to get the hiring dates and the employee names in key-value pairs , we could use JSON_OBJECTAGG and get the desired result.

What happens when we have duplicate keys? The value that gets picked for a particular key is non-deterministic in case of a grouped aggregate i.e. JSON_OBJECTAGG without the OVER() clause (The reason for the same is explained here). However, when used as a window function, we can use order by to make the result deterministic.

Let us look at another example:

One can see that based on the order,  the last value for the  key “Nethra” gets picked. As of now, JSON_OBJECTAGG() without OVER() clause does not support ordering.  So, using window functions could be an alternative to get over that limitation.

That is all, folks.  Please try out the new feature and let us know your feedback. Thanks for using MySQL!

 

About Chaithra Gopalareddy

Chaithra Gopalaredy has been working with MySQL database team since 2011. She is currently employed by Oracle, based in India, Bangalore. She is Principal Software Developer for the Optimizer Team of the MySQL database. She has a background in the database industry, working for Alcatel Lucent's DataBlitz main memory database in past.

One thought on “JSON specific window functions in MySQL 8.0

Leave a Reply