New JSON functions in MySQL 5.7.22

A number of new JSON functions have been added to MySQL 8.0. Since we appreciate that not everyone will be ready to upgrade to MySQL 8.0 the minute it is released, we have backported many of the new functions to MySQL 5.7 so that they are available starting with version 5.7.22. This is an overview of the new functions.

Revamped JSON merge functions

MySQL 5.7 came with a function called JSON_MERGE, which merged two JSON documents into one. The merged document would contain the union of the members of the two documents. For the members that existed in both of the input documents, the merged document would contain an array of the two original values. A very frequent feature request has been to add a variant of JSON_MERGE that uses the value from the second document instead of appending the two values when a member exists in both documents.

In MySQL 5.7.22 the JSON_MERGE function raises a deprecation warning that suggests that you instead use one of the following new functions:

  • JSON_MERGE_PRESERVE: This function has the same behavior as the old JSON_MERGE function. The suffix “_PRESERVE” has been added to signal that it “preserves” all the values from both of the documents.
  • JSON_MERGE_PATCH: This function implements the JSON Merge Patch algorithm specified in RFC 7396. With this algorithm, attribute values in the second document will overwrite values in the first document instead of appending them.

Examples:

JSON aggregation functions

Two new aggregation functions have been added:

  • JSON_ARRAYAGG: This function aggregates values into a JSON array.
  • JSON_OBJECTAGG: This function aggregates values into a JSON object.

For example, to create an array of the people working in the various departments of a company, you could use JSON_ARRAYAGG like this:

Similarly, JSON_OBJECTAGG can be used to collect properties of an item into a JSON object, like in this example copied from the reference manual:

You can read more about the JSON aggregation functions in Catalin’s blog post at https://mysqlserverteam.com/mysql-8-0-labs-json-aggregation-functions/.

Pretty-printing of JSON values

When JSON values are converted to text, MySQL uses a pretty compact format, where everything is on a single line. MySQL 5.7.22 introduces the JSON_PRETTY function, which formats the JSON value with line breaks and indentation to make it easier to read.

Inspection of JSON storage size

MySQL stores values in JSON columns in a binary format. The JSON_STORAGE_SIZE function lets you inspect how much space each JSON value takes in the database.

We hope you find these new functions useful. Thank you for using MySQL!

Leave a Reply