When MySQL 5.7 came out in 2015 with support for JSON fields and tools to manipulate JSON formatted data, I was very excited. I have found JSON support very useful when I want to store a chunk of data in a relational database when I'm not sure what that data is going to be, like a stack traces.
Now I have even more to look forward to in the upcoming release of MySQL 8.0. With two more release candidates listed, we still have to wait for the general availability release. However, I have outlined the most exciting changes.
You can now use JSON_ARRAYAGG instead of group concat! At some point in time, you have likely used group_concat to get all of the values for a grouping. With JSON_ARRAYAGG you can get the results as a JSON array instead of a delimited string! The MySQL documentation has a good example of how to use JSON_ARRAYAGG and its sister function JSON_OBJECTAGG.
Extract a object path without using JSON_EXTRACT. You can use field->'$.scans[*].level' in place of JSON_EXTRACRT(field, '$.scans[*].level'). This shorter notation is much cleaner than using a function.
This also provides support for ranges in arrays and adds a last keyword. This makes it a lot easier to interact with JSON arrays. field->'$.scans[last]', field->'$.scans[2 to 4]', and field->'$.scans[last-1]' are all valid, getting the last element, elements 2 through 4, and the second to last element.
The JSON_TABLE function simplifies taking JSON data and breaking it out like a relational table. This is a game-changer in handling json arrays in MySQL, as it bridges the gap between an object data store and relational database. Much like the aggregate JSON functions, MYSQL has a few good examples where they document JSON_TABLE.
Nothing is more frustrating than trying to dig through some JSON to find one specific line. JSON_PRETTY will reformat your JSON field or string to be easy to read.
You can also now do partial in place updates to JSON column values. With this change MySQL is starting to act more like an object store. You don't have to replace the whole field to change part of a JSON object or array stored in a field.
Regular Expression Changes
The next version of MySQL brings Multibyte safe regular expressions. The regular expression library now supports Unicode, and you can now safely use regular expressions on multibyte characters. A few new functions look like they will be useful for both queries and data clean up as well.
- REGEXP_LIKE functions like REGEXP or RLIKE in older versions of MySQL
- REGEXP_INSTR returns the starting index of the match much like using REGEXP or RLIKE. This is similar to REGEXP except it returns the string index and allows you to skip x matches in case you want to see if your expression matches 4 times instead of at least once.
- REGEXP_REPLACE is a straightforward search-replace. Sadly, it does not support mutable replacements with regular expression groups. This could be very useful for data clean up.
- REGEXP_SUBSTR returns the matching part of your regular expression.
Roles for Permissions
In the past when you wanted to grant many applications permissions to tables, you had to deal with each user individually. There was no way to set the same permissions to more than one user. The issue was worse when you wanted to add additional permissions, as you had to edit every user. MYSQL 8.0 adds support for roles, so you can grant permissions to a role and then apply a role to a user!