In MySQL 8 we familiarized JSON functionality into the MySQL Server. This effort involved the summary of a JSON data type, computer-generated columns and a set of about 20 SQL functions that allow you to operate and exploration JSON data on the server side.
The JSON functionality has been healthy acknowledged, and in MySQL 8.0 we have strategies to advance it in several behaviors. This post plans enhancements to the SQL functions with the adding of aggregate functions.
TL;DR
Opening with MySQL 8.0 (lab publication)* two new aggregation functions were further and can be used to syndicate data into JSON arrays/objects:
JSON_ARRAYAGG()
JSON_OBJECTAGG()
Now for the not as much of edgy:
Let’s reason about this situation: you have a database which covers both organized and semi-structured data and you’ve definite to accept the EAV model (Entity–Attribute–Value). The tables will appearance added or less like this:
You have a product table which comprises the public attributes:
Then you have the attribute table comprising all the non-common characteristics that a product capacity has:
With some likely admissions:
And in conclusion, the value table which associations the product key, the attribute key with the real value.
Now let’s insert a few products and their characteristics:
If you want to select whole products that associations all the attribute keys and values as JSON object, however joining the organized data (in product) and the semi-structured data (in characteristic and in value): you can usage the JSON_OBJECTAGG aggregation function:
To select an array covering all the likely attribute keys for to each product JSON_ARRAYAGG can be used:
The functions can also be used to benefit you drift to a new schema taking the semi-structured data kept in JSON columns:
Can this be accomplished in 5.7 too? Yes, but it’s more complex.
The adding of aggregate functions styles it cooler to aggregate data into JSON. Satisfy try out these new structures and let us see your response!
Thank you for using MySQL.