MySQL 8.0.x Offers New JSON Features
JavaScript Object Notation (JSON) is a lightweight, data interchange format widely used to store semi-structured and unstructured data, as well as interchange data with REST Web Services. MySQL database supports a native JSON data type in addition to several JSON related SQL/JSON functions. In this article, we will discuss some of the new JSON features in MySQL 8.x.
New Aggregate Functions
MySQL 8.0 added two new aggregate functions: JSON_ARRAYAG(), and JSON_OBJECTAGG(). The JSON_ARRAYAG() function aggregates a table column’s values into a single array. The JSON_OBJECTAGG() function aggregates two columns, or expressions, provided as arguments into a JSON object with the first column’s values as the keys, and the second column’s values as the values for the corresponding keys.
Partial Updates
Prior to MySQL 8, any updates to JSON documents made with the JSON_SET(), JSON_REPLACE(), and JSON_REMOVE() functions involved removing existing values, and adding new values. MySQL 8.0 made these functions more efficient by making partial, in-place updates if the updates do not involve adding new values.
Storage Utility Functions
A caveat for the partial updates discussed in the preceding section is that the updated values cannot take up more space than they did before the updates. Two new storage related utility functions JSON_STORAGE_SIZE(), and JSON_STORAGE_FREE() can be used to validate the condition. The JSON_STORAGE_SIZE() function returns the storage space in bytes for the binary representation of a JSON document prior to any partial updates. The JSON_STORAGE_FREE() function returns the free space for a column of type JSON after it has been partially updated with JSON_SET(), and JSON_REPLACE().
JSON Object Merge Functions
The JSON_MERGE() function, which merges two JSON objects preserving duplicate key values by appending the values in the second object to the first object’s values for the same key name, has been renamed to JSON_MERGE_PRESERVE(). A new merge function called JSON_MERGE_PATCH() has been added to patch the JSON objects being merged; implying that if the second object has a non-null value for a key that also appears in the first object, the key’s value is replaced by the second object’s key’s value. If the duplicate key’s value is null in the second object, the key is removed in the merge.
JSON Data as Relational Table
MySQL 8.0 added a new SQL/JSON function called JSON_TABLE() that returns a relational format table structure for JSON data, which could be JSON stored in a table column of type JSON, or raw JSON. The relational table, which has rows/columns format, returned by JSON_TABLE() should not be confused with the relational structure of a relational table in a database. Another new JSON function called JSON_VALUE() returns the value at a specified path within a JSON document.
Common Table Expressions
MySQL 8.0 added support for CTEs (Common Table Expressions). A CTE is a named temporary result set for a subquery that appears only within the scope of a single SQL statement. The benefit of CTEs is that subqueries associated with named temporary result sets may be used within a statement. A CTE may be non-recursive, implying that it does not refer to itself, or it may be recursive implying that it refers itself within a subquery.
Window Functions
MySQL 8.0 added support for Window functions. A Window function performs a per row calculation for a window of data, which is all the rows related with the row on which the calculation is being performed. Window functions are useful for statistical calculations such as cumulative distribution value, first/nth/last value, and percentage rank.
Functions to Validate JSON Schema
MySQL 8.0 added support for two new SQL/JSON functions to validate JSON documents. The JSON_SCHEMA_VALID() function validates a JSON document against a JSON schema and returns 1 (TRUE), or 0 (FALSE). The JSON_SCHEMA_VALIDATION_REPORT() function returns a detailed report about the validation as another JSON document.
These are only some of the salient new features in MySQL 8.0.x.
