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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> select j1, j2, json_merge_preserve(j1,j2) from t; +------------------+------------------+-------------------------------+ | j1 | j2 | json_merge_preserve(j1,j2) | +------------------+------------------+-------------------------------+ | {"a": 1, "b": 2} | {"b": 3, "c": 4} | {"a": 1, "b": [2, 3], "c": 4} | | {"a": 1, "b": 2} | {"b": null} | {"a": 1, "b": [2, null]} | +------------------+------------------+-------------------------------+ 2 rows in set (0,00 sec) mysql> select j1, j2, json_merge_patch(j1,j2) from t; +------------------+------------------+--------------------------+ | j1 | j2 | json_merge_patch(j1,j2) | +------------------+------------------+--------------------------+ | {"a": 1, "b": 2} | {"b": 3, "c": 4} | {"a": 1, "b": 3, "c": 4} | | {"a": 1, "b": 2} | {"b": null} | {"a": 1} | +------------------+------------------+--------------------------+ 2 rows in set (0,00 sec) |
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:
|
1 2 3 4 5 6 7 8 9 10 |
mysql> select dept, json_arrayagg(name) as employees from employees group by dept; +-----------------+------------------+ | dept | employees | +-----------------+------------------+ | Engineering | ["Joe", "Sue"] | | Human Resources | ["Peter"] | | QA | ["Jack"] | | Sales | ["Joe", "Jenny"] | +-----------------+------------------+ 4 rows in set (0,00 sec) |
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:
|
1 2 3 4 5 6 7 8 |
mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value) FROM t3 GROUP BY o_id; +------+---------------------------------------+ | o_id | JSON_OBJECTAGG(attribute, value) | +------+---------------------------------------+ | 2 | {"color": "red", "fabric": "silk"} | | 3 | {"color": "green", "shape": "square"} | +------+---------------------------------------+ 2 rows in set (0,00 sec) |
You can read more about the JSON aggregation functions in Catalin’s blog post at /blog-archive/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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> SELECT JSON_PRETTY('[{"name":"Knut", "dept":"Engineering"},{"name":"Catalin","dept":"Engineering"}]') as pretty; +---------------------------------------------------------------------------------------------------------------------+ | pretty | +---------------------------------------------------------------------------------------------------------------------+ | [ { "dept": "Engineering", "name": "Knut" }, { "dept": "Engineering", "name": "Catalin" } ] | +---------------------------------------------------------------------------------------------------------------------+ 1 row in set (0,00 sec) |
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.
|
1 2 3 4 5 6 7 8 9 |
mysql> select json_col, json_storage_size(json_col) from t1; +--------------------------+-----------------------------+ | json_col | json_storage_size(json_col) | +--------------------------+-----------------------------+ | true | 2 | | ["a", "b", 123, 45] | 21 | | {"w": 123456, "x": "yz"} | 28 | +--------------------------+-----------------------------+ 3 rows in set (0,00 sec) |
We hope you find these new functions useful. Thank you for using MySQL!