Using the new JSON Functions in MySQL 8
JSON (JavaScript Object Notation) is the web’s most used data interchange format. It is a low-overhead alternative to XML in web services. And MySQL is the #1 open-source database. The JSON-MySQL pair is often used. However, until the latest MySQL version, it lacked some needed functions. However, in this article, you’ll learn about open-source MySQL’s new JSON-related functions and features.
Discover how at OpenReplay.com.
The new functions enhance how you use JSON (JavaScript Object Notation) data. The new JSON functions and improvements to existing ones will let you process JSON data in new ways. This article includes examples and discussion based on a real-world magazine catalog. All you need to get started is to download and install the MySQL Community Server. To run the SQL code examples in the article, you’ll need mysql — The MySQL Command-Line Client.
Next, I discuss the new JSON-related enhancements.
Displaying JSON Data as a Relational Table
Displaying data in rows and columns is relational. JSON is far from ideal for this. To fix the shortcoming, MySQL 8.0.4 added a new JSON function. It is called JSON_TABLE(). It takes a JSON document as input and outputs the JSON data in a table with rows and columns. The syntax of the function is as follows:
JSON_TABLE(expr, path COLUMNS (column_list) [AS] aliasThe first parameter expr is the JSON document. The path parameter has the row data for the table shown. The COLUMNS clause lists the columns to include in the generated table. You must provide an alias. The JSON document is the JSON to display in a relational-like table. It could be raw JSON or a JSON document from a MySQL table column of type JSON.
Next, let’s illustrate the JSON_TABLE() function with an example. We use the raw JSON document listed below as a data source for the relational table:
[
{
MagazineName: "Magazine1",
MagazineEdition: "Nov 2023",
MagazineOnline: "true",
},
{
MagazineName: "Magazine2",
MagazineEdition: "December 2023",
MagazineOnline: "true",
},
{
MagazineName: "Magazine3",
MagazineEdition: "Nov 2023",
MagazineOnline: "false",
},
];Generate a table with three columns. The columns are MagazineName, MagazineEdition, and MagazineOnline. Use thisSELECT statement. The SELECT query abbreviates the preceding JSON document as […] for conciseness:
mysql> SELECT *
FROM json_table('[...]',"$[*]" columns(magazinename VARCHAR(100) path "$.MagazineName",magazineedition VARCHAR(100) path "$.MagazineEdition",magazineonline VARCHAR(100) path "$.MagazineOnline")) AS example_json_table;The result is the relational table:
+--------------+-----------------+----------------+
| MagazineName | MagazineEdition | MagazineOnline |
+--------------+-----------------+----------------+
| Magazine1 | Nov 2023 | true |
| Magazine2 | December 2023 | true |
| Magazine3 | Nov 2023 | false |
+--------------+-----------------+----------------+The path for the data rows in the preceding example is $[*], the root document. You can apply the path to any specific data section within a JSON document. As an example, use path $[2] to select only the third row of the JSON document-array provided as data source. The following SELECT statement using path $[2] outputs only one row of data in the relational table:
mysql> SELECT *
FROM json_table('[...]',"$[2]" columns(magazinename VARCHAR(100) path "$.MagazineName",magazineedition VARCHAR(100) path "$.MagazineEdition",magazineonline VARCHAR(100) path "$.MagazineOnline")) AS example_json_table;The result is the relational table:
+--------------+-----------------+----------------+
| MagazineName | MagazineEdition | MagazineOnline |
+--------------+-----------------+----------------+
| Magazine3 | Nov 2023 | false |
+--------------+-----------------+----------------+The JSON_TABLE() function has an advanced syntax. It includes the <expr> ON EMPTY clause for an alternative output if the data is empty. It also has the <expr> ON ERROR clause for when an error occurs.
Using JSON Aggregation Functions as Window Functions
What do the aggregation functions do? As the name suggests, they aggregate, or collect, data and output a single data element. MySQL has long had aggregation functions like SUM() and AVG(). They were available in earlier versions. JSON_ARRAYAGG() and JSON_OBJECTAGG() have been available since MySQL 5.7. The functions can group values into subsets. They do this using the GROUP BY clause. The JSON_ARRAYAGG() function combines values. They may or may not be JSON. It puts them into a single JSON array. The JSON_OBJECTAGG() function combines values into a single JSON object. What is new since MySQL 8.0.14 is the support for these two JSON aggregation functions as Window functions. What are window functions ?Window functions were added to MySQL in version 8.0. They perform a calculation over data near a given row in a query. You may find that to be quizzical initially because it is unlike most other functions. To explain further, for each row in the query, a Window function runs a query over a window of data related to the row. You can use the same aggregation functions as Window functions by adding an OVER clause in the query. The syntax for the two JSON aggregation functions changes when they are used as Window functions:
JSON_ARRAYAGG(col_or_expr) [over_clause]
JSON_OBJECTAGG(key, value) [over_clause]Next, we demonstrate the use of the two JSON aggregation functions when they are used as Window functions. A common use of Window functions is to compare a data value over an aggregated value for each row of data. For example, a magazine publisher may want to compare the sales of its magazines with:
- The publisher’s total magazine sales.
- The total magazine sales for all publishers.
To use an example, put sales data for publishers and magazines into a table called “magazine”:
mysql> create table magazine (id integer, publisher varchar(50), magazine_name varchar(50), sales int);
mysql> INSERT INTO magazine
(id,
publisher,
magazine_name,
sales)
VALUES (1,
'publisher_1',
'magazine_1',
60),
(1,
'publisher_1',
'magazine_2',
100),
(1,
'publisher_1',
'magazine_3',
30),
(2,
'publisher_2',
'magazine_4',
50),
(2,
'publisher_2',
'magazine_5',
75),
( 2,
'publisher_2',
'magazine_6',
60),
(3,
'publisher_3',
'magazine_7',
50),
(3,
'publisher_3',
'magazine_8',
25),
(3,
'publisher_3',
'magazine_9',
100),
(3,
'publisher_3',
'magazine_10',
125); Next, use JSON_ARRAYAGG() as a Window function. Do this with the SQL SELECT statement:
mysql> SELECT id, publisher,JSON_ARRAYAGG(magazine_name) OVER w magazine_name_a
rr, sales AS m_sales, SUM(sales) OVER(PARTITION BY publisher) AS p_sales, SUM(sales) OVER() AS t_sales FROM
-> magazine WINDOW w AS (PARTITION BY publisher ORDER BY id);In the result below (truncated for clarity), notice how the total sales for the magazine’s publisher is also listed for each row of data, which is about a single magazine. And, the total magazine sales over all magazine publishers is also listed for each row of data:
+------+-------------+-----------------------------------------------+-------------------+--------+
| id | publisher | magazine_name_arr | m_sales | p_sales |t_sales |
+------+-------------+-----------------------------------------------+-------------------+--------+
| 1 | publisher_1 | ["magazine_1", "magazine_2", "magazine_3"] | 60 | 190 | 675 |
| 1 | publisher_1 | ["magazine_1", "magazine_2", "magazine_3"] | 100 | 190 | 675 |
| 1 | publisher_1 | ["magazine_1", "magazine_2", "magazine_3"] | 30 | 190 | 675 |
| 2 | publisher_2 | ["magazine_4", "magazine_5", "magazine_6"] | 50 | 185 | 675 |
| 2 | publisher_2 | ["magazine_4", "magazine_5", "magazine_6"] | 75 | 185 | 675 |
| 2 | publisher_2 | ["magazine_4", "magazine_5", "magazine_6"] | 60 | 185 | 675 |
| 3 | publisher_3 | ["magazine_7", "magazine_8", "magazine_9",...]| 50 | 300 | 675 |
| 3 | publisher_3 | ["magazine_7", "magazine_8", "magazine_9",...]| 25 | 300 | 675 |
| 3 | publisher_3 | ["magazine_7", "magazine_8", "magazine_9",...]| 100 | 300 | 675 |
| 3 | publisher_3 | ["magazine_7", "magazine_8", "magazine_9",...]| 125 | 300 | 675 |
+------+-------------+-----------------------------------------------+---------+---------+--------+This type of window aggregation makes it easier to compare data, especially sales data.
Next, use the same example table from the magazine to run the JSON_OBJECTAGG() function. This function needs two arguments, one for the key and the other for the value in a key/value pair in a JSON object. Run the following SQL SELECT statement. It shows the use of JSON_OBJECTAGG() as a Window function:
mysql> Select DISTINCT id, JSON_OBJECTAGG(magazine_name,sales) OVER w magazine_object, SUM(sales) OVER(PARTITION BY publisher) AS publisher_sales, SUM(sales) OVER() AS total_sales
-> FROM magazine WINDOW w AS (PARTITION BY publisher ORDER BY id);This time, an aggregated JSON object is output for each row of matched data (output truncated for clarity):
+------+-------------------------------------------------------------+-----------------+-------------+
| id | magazine_object | publisher_sales | total_sales |
+------+-------------------------------------------------------------+-----------------+-------------+
| 1 | {"magazine_1": 60, "magazine_2": 100, "magazine_3": 30} | 190 | 675 |
| 2 | {"magazine_4": 50, "magazine_5": 75, "magazine_6": 60} | 185 | 675 |
| 3 | {"magazine_7": 50, "magazine_8": 25, "magazine_9": 100,...} | 300 | 675 |
+------+-------------------------------------------------------------+-----------------+-------------+How to use a Non-Null Default Value for the JSON Data type
A default value is the value used to add data to the column when no explicit value is provided for the column when adding a row. You specify a default value with a DEFAULT clause. MySQL 8 added support for using expressions with the DEFAULT clause. Notably, the JSON data type can have a default value other than non-null in version 8.0.13. But, you can only set it using the DEFAULT clause with a default value expression. Before 8.0.13 the JSON data type could not be assigned a non-NULL default value. The following example demonstrates using the DEFAULT clause. It uses an expression to set a default value for a JSON-type column. In the example, we also set default values for some other data types, and, as previously, abbreviate the JSON document as […]`:
mysql> CREATE TABLE t1 (
c1 INT DEFAULT -1,
c2 JSON DEFAULT ('[...]'),
c3 TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);Next, add data for only the c1 column. We do not add values for other columns. We do this to show adding default value for the JSON data type:
mysql> INSERT INTO t1(c1) VALUES(1);Because we set the default value in the table definition, the JSON type column c2 is assigned the default value as shown in a SQL query:
mysql> select * from t1 \G
*************************** 1. row ***************************
c1: 1
c2: [{"MagazineName": "Magazine1", "MagazineOnline": "true", "MagazineEdition":
"Nov 2023"}, {"MagazineName": "Magazine2", "MagazineOnline": "true", "MagazineEdition": "December 2023"}, {"MagazineName": "Magazine3", "MagazineOnline": "false
", "MagazineEdition": "Nov 2023"}]
c3: 2023-12-05 18:40:42How to Merge JSON Values
Before MySQL 8.0.3, it only supports one merge function called JSON_MERGE(). The function merges two JSON documents as follows:
- Merges adjacent arrays into a single array
- Merges adjacent objects into a single object
- Merges adjacent object and array into a single array by first auto-wrapping the object as an array
- Merges a scalar as an array by first auto-wrapping the scalar as an array
The JSON_MERGE() function does not cover the use case in which a user wants to merge two documents by patching the first document with the second document. Version 8.0.3 added a new function called JSON_MERGE_PATCH() to merge-patch JSON documents. The syntax of the function to merge-patch two or more JSON documents is as follows:
JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)If two JSON documents to be merged are both objects, the merge-patch is done as discussed next. If the two objects being merged have a member in each with a matching key, the result has only one key. Its value is taken from the second document. The next two examples show the use of a merge-patch. In the first example, the two merged JSON documents have a common key. It’s called magazine_name. The value of the key is different in the two JSON objects. The resulting document includes only the key value from the second object. We are patching, not merging:
mysql> SELECT JSON_MERGE_PATCH('{"magazine_name": "Magazine1"}', '{"magazine_name": "MySQL Magazine"}') AS MagazineName \G
*************************** 1. row ***************************
MagazineName: {"magazine_name": "MySQL Magazine"}The merge-patch does not consider the lexicographical ordering of the values being merge-patched. In the next example, the key magazine_name has a higher value in the first object. The letter M comes before the letter S. But the result has the value from the second object:
mysql> SELECT JSON_MERGE_PATCH('{"magazine_name":"MySQL Magazine"}', '{"magazine_name": "SQL Magazine"}') AS MagazineName \G
*************************** 1. row ***************************
MagazineName: {"magazine_name": "SQL Magazine"}If a matching key’s value is null in the second document being merged, the result document does not include the key. In the next example, the second document has a key called b with value null. The resulting document does not include key b:
mysql> SELECT JSON_MERGE_PATCH('{"magazine_name":"SQL Magazine","magazine_edition":"Nov. 2023"}', '{"magazine_name": "MySQL Magazine","magazine_edition":null}') AS MagazineCatalog \G
*************************** 1. row ***************************
MagazineCatalog: {"magazine_name": "MySQL Magazine"}That is not to say the function doesn’t handle null values. Here is an example. The key magazine_edition with a null value in the first document is kept in the result. The second document lacks a key called magazine_edition. So, it keeps the value from the first document. In other words, we take the value for the key from the first document:
mysql> SELECT JSON_MERGE_PATCH('{"magazine_name":"SQL Magazine","magazine_edition":null}','{"magazine_name": "MySQL Magazine"}') AS MagazineCatalog \G
*************************** 1. row ***************************
MagazineCatalog: {"magazine_name": "MySQL Magazine", "magazine_edition": null}A null value in the first document is not kept in the result if the second document also has a matching key with a null value. An example is the key magazine_edition with a null value in both documents being merged:
mysql> SELECT JSON_MERGE_PATCH('{"magazine_name":"SQL Magazine","magazine_edition":null}','{"magazine_name": "MySQL Magazine","magazine_edition":null}') AS MagazineCatalog \G
*************************** 1. row ***************************
MagazineCatalog: {"magazine_name": "MySQL Magazine"}The result does not include a null key value in the second document. This happens even when the null value is not a matching key’s value :
mysql> SELECT JSON_MERGE_PATCH('{"magazine_name":"SQL Magazine"}','{"magazine_name": "MySQL Magazine","magazine_edition":null}') AS MagazineCatalog \G
*************************** 1. row ***************************
MagazineCatalog: {"magazine_name": "MySQL Magazine"}The JSON_MERGE_PATCH() function is mainly to merge and patch a JSON object. It does this with a second document that is also a JSON object. If the first document is not a JSON object, it is like merging an empty object with the second document. The result is the second document, as in this example:
mysql> SELECT JSON_MERGE_PATCH('["magazine_name","SQL Magazine"]','{"magazine_name": "MySQL Magazine"}') AS MagazineCatalog \G
*************************** 1. row ***************************
MagazineCatalog: {"magazine_name": "MySQL Magazine"}The second document can be any type, not just a JSON object. The merge result is the second document if it is not a JSON object. For example, when the second document is a JSON array, the result is the array:
mysql> SELECT JSON_MERGE_PATCH('{"magazine_name": "MySQL Magazine"}','["magazine_name","MySQL Magazine"]') AS MagazineCatalog \G
*************************** 1. row ***************************
MagazineCatalog: ["magazine_name", "MySQL Magazine"]In fact, neither document has to be a JSON object. Here’s an example in which both documents are JSON arrays:
mysql> SELECT JSON_MERGE_PATCH('["magazine_name","SQL Magazine"]','["magazine_name","MySQL Magazine"]') AS MagazineCatalog \G
*************************** 1. row ***************************
MagazineCatalog: ["magazine_name", "MySQL Magazine"]The two documents being merged do have to be JSON documents, or an error will occur, as in the example:
mysql> SELECT JSON_MERGE_PATCH('["magazine_name","SQL Magazine"]',2) AS MagazineCatalog \G
ERROR 3146 (22032): Invalid data type for JSON data in argument 2 to function json_merge_patch; a JSON string or JSON type is required.You can merge multiple (more than two) JSON documents in a single function call. For example, the following function call merges three documents. They have the same key, magazine_name. The resulting document includes the key’s value from the third document:
mysql> SELECT JSON_MERGE_PATCH('{"magazine_name":"MySQL Magazine"}', '{"magazine_name": "SQL Magazine"}','{"magazine_name":"JSON Magazine"}') AS MagazineName \G
*************************** 1. row ***************************
MagazineName: {"magazine_name": "JSON Magazine"}The JSON_MERGE_PATCH() function is RFC 7396 compliant.
The JSON_MERGE() function is now called JSON_MERGE_PRESERVE(). This is only a renaming. It has the same functionality:
Using the New and Concise column->>path Operator
MySQL 8 introduces a new operator with the syntax column->>path as an inline path operator. The column -> path operator was already available as a short-form for extracting a value. It is the same as calling the JSON_EXTRACT(column, path) function. The column->>path operator takes it further by unquoting the extracted value using the JSON_UNQUOTE(json_val) function. The column->>path operator is equal to calling JSON_UNQUOTE(JSON_EXTRACT()).
To show the new operator, create a table and add some JSON data:
mysql> CREATE TABLE magazine(c JSON);
mysql> INSERT INTO magazine(c) VALUES('{"MagazineName":"Magazine1","MagazineEdition":"Nov 2023","MagazineOnline":"true"}'),('{"MagazineName":"Magazine2","MagazineEdition":"December 2023","MagazineOnline":"true"}'),('{"MagazineName":"Magazine3","MagazineEdition":"Nov 2023","MagazineOnline":"false"}');You can use a simple SELECT statement with the c-> operator to extract a value as follows:
mysql> SELECT c->'$.MagazineName' AS name FROM magazine;
+-------------+
| name |
+-------------+
| "Magazine1" |
| "Magazine2" |
| "Magazine3" |
+-------------+To remove the quotes from the values in the result, you can use the JSON_UNQUOTE() function as shown below:
mysql> SELECT JSON_UNQUOTE(c->'$.MagazineName') AS name FROM magazine;
+-----------+
| name |
+-----------+
| Magazine1 |
| Magazine2 |
| Magazine3 |
+-----------+Or, for conciseness, the new inline path operator can be used. It combines two function calls like this:
mysql> SELECT c->>'$.MagazineName' AS name FROM magazine;
+-----------+
| name |
+-----------+
| Magazine1 |
| Magazine2 |
| Magazine3 |
+-----------+How to Perform Partial In-place Updates
What is the old method for performing an update on a table column of type JSON? Before MySQL 8.0.2, any update with the UPDATE statement involving the JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() functions removes an existing document in its entirety. Then, it adds the new JSON document with updated values. Removing and adding a complete JSON document could be slow. This is especially true if the update involves only a small part of a large JSON document. The partial in-place updates feature is an optimization that helps performance. It’s available in version 8.0.3. JSON type column values have been updated in place. This happens when using the UPDATE statement with the JSON_SET(), JSON_REPLACE(), and JSON_REMOVE() functions. You can use partial in-place updates to only update existing array and object values. You can’t use them to add new elements to an object or array. The replacement values cannot occupy more space than those being updated unless earlier partial updates have cleared enough storage space for the new values. The function JSON_STORAGE_SIZE() gets the storage space needed for a JSON document in a JSON type column. This is before any partial updates. If the storage space is freed up with a partial in-place update using any of the three functions that support partial in-place updates, you can get the amount of freed space with the JSON_STORAGE_FREE() function.
The JSON_SET() function inserts or updates values. Its syntax is:
JSON_SET(json_doc, path, val[, path, val] ...)To show a partial, in-place update using the JSON_SET() function, create a table called t1. The table has a JSON type column called jdoc:
mysql> CREATE TABLE t1 (jdoc JSON);Add some JSON data to the table:
mysql> INSERT INTO t1 VALUES('{"publisher": "publisher_1", "magazine": "magazine_1", "id": 1}');
mysql> INSERT INTO t1 VALUES('{"publisher": "publisher_2", "magazine": "magazine_2", "id": 2}');Update the publisher column in place. Use the UPDATE statement with the JSON_SET() function. Do it like this:
mysql> UPDATE t1 SET jdoc = JSON_SET(jdoc, '$.publisher', 'MySQL');The two column values get updated as shown in the result for the following query:
mysql> SELECT * from t1;
+-----------------------------------------------------------+
| jdoc |
+-----------------------------------------------------------+
| {"id": 1, "magazine": "magazine_1", "publisher": "MySQL"} |
| {"id": 2, "magazine": "magazine_2", "publisher": "MySQL"} |
+-----------------------------------------------------------+Likewise, update the magazine column values:
mysql> UPDATE t1 SET jdoc = JSON_SET(jdoc, '$.magazine', 'MySQL Magazine');The two magazine columns get updated as well:
mysql> select * from t1;
+---------------------------------------------------------------+
| jdoc |
+---------------------------------------------------------------+
| {"id": 1, "magazine": "MySQL Magazine", "publisher": "MySQL"} |
| {"id": 2, "magazine": "MySQL Magazine", "publisher": "MySQL"} |
+---------------------------------------------------------------+You can make multiple column value updates in-place as follows:
mysql> UPDATE t1 SET jdoc = JSON_SET(jdoc, '$.publisher', 'MySQL Publishing', '$
.magazine', 'MySQL Mag','$.edition', 'Jan 2010');The result shows the two columns publisher and magazine as updated:
mysql> SELECT * from t1;
+--------------------------------------------------------------------------------------------+
| jdoc |
+--------------------------------------------------------------------------------------------+
| {"id": 1, "edition": "Jan 2010", "magazine": "MySQL Mag", "publisher": "MySQL Publishing"} |
| {"id": 2, "edition": "Jan 2010", "magazine": "MySQL Mag", "publisher": "MySQL Publishing"} |
+--------------------------------------------------------------------------------------------+You can replace a column value in place using the JSON_REPLACE() function. The next example replaces complete documents with new ones:
mysql> UPDATE t1 SET jdoc = JSON_REPLACE(jdoc, '$','MySQL Catalog');Two documents get replaced:
mysql> SELECT * FROM t1;
+-----------------+
| jdoc |
+-----------------+
| "MySQL Catalog" |
| "MySQL Catalog" |
+-----------------+Another example of replacing an existing document with a new one that is a JSON object is as follows:
mysql> UPDATE t1 SET jdoc = JSON_REPLACE(jdoc, '$','{"id": 1, "edition": "Jan 20
10", "magazine": "MySQL Mag", "publisher": "MySQL
'> Publishing"}');A query lists the resulting document:
mysql> SELECT * FROM t1;
+-----------------------------------------------------------------------------------------------------------+
| jdoc |
+-----------------------------------------------------------------------------------------------------------+
| "{\"id\":1, \"edition\": \"Jan 2010\", \"magazine\": \"MySQL Mag\", \"publisher\": \"MySQL\nPublishing\"}"|
| "{\"id\":1, \"edition\": \"Jan 2010\", \"magazine\": \"MySQL Mag\", \"publisher\": \"MySQL\nPublishing\"}"|
+-----------------------------------------------------------------------------------------------------------+You can use an in-place update to remove selected columns. Use the JSON_REMOVE() function. It has the following syntax:
JSON_REMOVE(json_doc, path[, path] ...)As an example, remove the id and magazine columns:
mysql> UPDATE t1 SET jdoc = JSON_REMOVE(jdoc,'$.id','$.magazine');The resulting document has the two columns removed:
mysql> SELECT * from t1;
+--------------------------------------------------------------+
| jdoc |
+--------------------------------------------------------------+
| {"edition": "Jan 2010", "publisher": "MySQL \n\nPublishing"} |
+--------------------------------------------------------------+Finding if two JSON Documents Overlap
First, it is important to define what document overlapping means. Two JSON documents overlap if they share any key/value pairs in an object, or if they share any array elements. A new function introduced in MySQL 8.0.17 compares two JSON documents and returns 1, implying true if the two JSON documents overlap. The function is JSON_OVERLAPS(json_doc1, json_doc2).
Use the new function with two JSON documents that don’t have any common elements. As the two documents don’t overlap, the result is 0:
mysql> SELECT JSON_OVERLAPS("[1,2,3,4,5]", "[6,7,8]");
+-----------------------------------------+
| JSON_OVERLAPS("[1,2,3,4,5]", "[6,7,8]") |
+-----------------------------------------+
| 0 |
+-----------------------------------------+And in a modified example that includes a common array element 5, the result is 1:
mysql> SELECT JSON_OVERLAPS("[1,2,3,4,5]", "[5,6,7,8]");
+-------------------------------------------+
| JSON_OVERLAPS("[1,2,3,4,5]", "[5,6,7,8]") |
+-------------------------------------------+
| 1 |
+-------------------------------------------+The common array element could even be null for the function to consider it to be an overlap, as in the next example:
mysql> SELECT JSON_OVERLAPS("[1,2,3,4,5,null]", "[6,7,8,null]");
+---------------------------------------------------+
| JSON_OVERLAPS("[1,2,3,4,5,null]", "[6,7,8,null]") |
+---------------------------------------------------+
| 1 |
+---------------------------------------------------+If you compare two JSON objects with no common key-value pair, the function returns 0:
mysql> SELECT JSON_OVERLAPS('{"a": 1, "b": 2}', '{"c": 2, "d": 2}');
+-------------------------------------------------------+
| JSON_OVERLAPS('{"a": 1, "b": 2}', '{"c": 2, "d": 2}') |
+-------------------------------------------------------+
| 0 |
+-------------------------------------------------------+If we modify the same example slightly to include a common key/value pair, the function will return 1:
mysql> SELECT JSON_OVERLAPS('{"a": 1, "b": 2}', '{"a": 1, "d": 2}');
+-------------------------------------------------------+
| JSON_OVERLAPS('{"a": 1, "b": 2}', '{"a": 1, "d": 2}') |
+-------------------------------------------------------+
| 1 |
+-------------------------------------------------------+The function compares for an overlap in both the key name and the value. Both the key and value in a pair have to match. Two documents that share a common key but have different key values return false, or 0, with the function:
mysql> SELECT JSON_OVERLAPS('{"a": 1, "b": 2}', '{"a": 2, "d": 2}');
+-------------------------------------------------------+
| JSON_OVERLAPS('{"a": 1, "b": 2}', '{"a": 2, "d": 2}') |
+-------------------------------------------------------+
| 0 |
+-------------------------------------------------------+A common key’s value could even be null for the function to consider it to be an overlap, as in the example:
mysql> SELECT JSON_OVERLAPS('{"a": null, "b": 2}', '{"a": null, "d": 2}');
+-------------------------------------------------------------+
| JSON_OVERLAPS('{"a": null, "b": 2}', '{"a": null, "d": 2}') |
+-------------------------------------------------------------+
| 1 |
+-------------------------------------------------------------+How to Extract a Value at a Given Path in a JSON Document
How often have you needed to extract a value at a given path in a JSON document? MySQL 8.0.21 introduced a new function called JSON_VALUE(json_doc, path) for just that. The function’s full syntax includes a provision to convert the extracted value to a specified type. In the RETURNING clause, you can specify the type that will return the extracted value:
JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])As a first example, we return the value of key b in a JSON object:
mysql> SELECT JSON_VALUE('{"a": 1, "b": 2, "c": 3}', '$.b');
+-----------------------------------------------+
| JSON_VALUE('{"a": 1, "b": 2, "c": 3}', '$.b') |
+-----------------------------------------------+
| 2 |
+-----------------------------------------------+If the extracted value’s type should be a decimal value, add the RETURNING clause with the DECIMAL type as follows:
mysql> SELECT JSON_VALUE('{"a": 1, "b": 2, "c": 3}', '$.b' RETURNING DECIMAL(10,
2)) AS d;
+------+
| d |
+------+
| 2.00 |
+------+The supported function return types include FLOAT, DOUBLE, DECIMAL, SIGNED, UNSIGNED, DATE, TIME, DATETIME, CHAR, and JSON. In the next example, the function returns a JSON document:
mysql> SELECT JSON_VALUE('{"a": 1, "b": 2, "c": 3}', '$' RETURNING JSON) AS j;
+--------------------------+
| j |
+--------------------------+
| {"a": 1, "b": 2, "c": 3} |
+--------------------------+The function returns a CHAR value in the next example:
mysql> SELECT JSON_VALUE('{"a": 1, "b": 2, "c": 3}', '$.a' RETURNING CHAR) AS j;
+------+
| j |
+------+
| 1 |
+------+The JSON_VALUE() function simplifies the task of creating indexes on JSON type columns. Typically, to create an index on a JSON column, you would first make a generated column. Then, you would create the index on the generated column, as in this example:
mysql> CREATE TABLE t2 (
-> j JSON,
-> g INT GENERATED ALWAYS AS (j->"$.idx"),
-> INDEX i1 (g)
-> );You can create the same index on column j of type JSON using the JSON_VALUE() function. You can do this without first creating a generated column. The same index on the column j of type JSON can be created by using the JSON_VALUE() function as follows without having to first create a generated column:
mysql> CREATE TABLE t1(
-> j JSON,
-> INDEX i1 ((JSON_VALUE(j, '$.id' RETURNING UNSIGNED)))
-> );How to Find if a Value is a Member Element of a JSON Array
MySQL 8.0.17 introduced a new function value MEMBER OF(json_array) to find if a value is a member of a given JSON array.
The function returns 1 if the value is a member of the array. The function returns 0 if the value is not a member. In the following example, because the value 3 is a member of the array in the second operand, the function returns 1:
mysql> SELECT 3 MEMBER OF('[1, 2, 3,"abc"]');
+--------------------------------+
| 3 MEMBER OF('[1, 2, 3,"abc"]') |
+--------------------------------+
| 1 |
+--------------------------------+The function returns 0 in the next example because the value 3 is not a member of the given array:
mysql> SELECT 3 MEMBER OF('[1, 2,"abc"]');
+-----------------------------+
| 3 MEMBER OF('[1, 2,"abc"]') |
+-----------------------------+
| 0 |
+-----------------------------+The value must be a scalar value or a JSON document. The next example determines if a scalar string value is a member of an array:
mysql> SELECT "abc" MEMBER OF('[1, 2,"abc"]');
+---------------------------------+
| "abc" MEMBER OF('[1, 2,"abc"]') |
+---------------------------------+
| 1 |
+---------------------------------+The following example determines if a scalar boolean value true is a member of an array:
mysql> SELECT true MEMBER OF('[1, 2,"abc",true]');
+-------------------------------------+
| true MEMBER OF('[1, 2,"abc",true]') |
+-------------------------------------+
| 1 |
+-------------------------------------+If the value to be tested is null, the result is always NULL. This is regardless of whether the array includes a null element:
mysql> SELECT null MEMBER OF('[1, 2,"abc",true,null]');
+------------------------------------------+
| null MEMBER OF('[1, 2,"abc",true,null]') |
+------------------------------------------+
| NULL |
+------------------------------------------+In the following example, a JSON array is tested for membership in another array. The second array includes the first array as an element. Therefore, the result is 1:
mysql> SELECT '[1,2,3]' MEMBER OF('[1, 2,"[1,2,3]"]');
+-----------------------------------------+
| '[1,2,3]' MEMBER OF('[1, 2,"[1,2,3]"]') |
+-----------------------------------------+
| 1 |
+-----------------------------------------+How to Validate a JSON Document with a Schema
By default, JSON is a schema-less data structure, but it supports schemas if needed. MySQL 8.0.17 added two new functions for validating a JSON document with a given schema. The first function is JSON_SCHEMA_VALID(schema,document). It validates the JSON document in the first argument with the schema in the second. It returns 1 if the JSON document is valid. To show with an example, set a user-defined variable called @schema to a schema:
mysql> SET @schema = '{
'> "$schema": "https://json-schema.org/draft/2020-12/schema",
'> "$id": "https://example.com/product.schema.json",
'> "title": "Catalog",
'> "description": "A catalog",
'> "type": "object",
'> "properties": {
'> "catalogId": {
'> "description": "Catalog id",
'> "type": "integer"
'> },
'> "catalogName": {
'> "description": "Catalog name",
'> "type": "string"
'> },
'> "edition": {
'> "description": "Catalog edition",
'> "type": "string"
'> }
'> },
'> "required": [ "catalogId", "catalogName", "edition" ]
'> }';Set a second variable, say @document, to the JSON document to validate:
mysql> SET @document = '{
'> "catalogId": 1,
'> "catalogName": "MySQL Magazine Catalog",
'> "edition": "First Edition"
'> }';Validate the JSON document using the function. A return value of 1 indicates that the document is valid:
mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
+---------------------------------------+
| 1 |
+---------------------------------------+The schema includes a “required” key to specify the required keys in a JSON document. The “required” key is set as "required": [ "catalogId", "catalogName", "edition" ] in the example. Set a user-defined variable for another JSON document that doesn’t include a required key:
mysql> SET @document = '{
'> "catalogId": 1,
'> "catalogName": "MySQL Magazine Catalog"
'> }';Validate against the same schema. This time, the result is ‘0`. This indicates that the document is not valid:
mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
+---------------------------------------+
| 0 |
+---------------------------------------+The example schema specifies the type for each of the JSON document elements. If a document includes an element that matches the name but not the type, again, the document is not valid. To show an example, set a user-defined variable to a JSON document that specifies an integer value for edition. The edition actually has the type defined as string in the schema:
mysql> SET @document = '{
'> "catalogId": 1,
'> "catalogName": "MySQL Magazine Catalog",
'> "edition": 1
'> }';Validate the document, and the result is 0:
mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
+---------------------------------------+
| 0 |
+---------------------------------------+The JSON_SCHEMA_VALID() function can be used to enforce CHECK constraints. To show with an example, create a table that includes a CHECK constraint:
mysql> CREATE TABLE test (
-> catalog JSON,
-> CHECK(
-> JSON_SCHEMA_VALID(
-> '{
'> "type": "object",
'> "properties": {
'> "catalogId": {
'> "description": "Catalog id",
'> "type": "integer"
'> },
'> "catalogName": {
'> "description": "Catalog name",
'> "type": "string"
'> },
'> "edition": {
'> "description": "Catalog edition",
'> "type": "string"
'> }
'> },
'> "required": [ "catalogId", "catalogName", "edition" ]
'> }',catalog
-> )
-> )
-> );Add some data to the `JSON-type column. If the data added is not valid, an error message is output as in the example:
mysql> INSERT INTO test(catalog)
-> VALUES (
-> '{
'> "catalogId": 1,
'> "catalogName": "MySQL Magazine Catalog",
'> "edition": 1
'> }'
-> );
ERROR 3819 (HY000): Check constraint 'test_chk_1' is violated.Adding a valid JSON document doesn’t raise an error:
mysql> INSERT INTO test(catalog)
-> VALUES (
-> '{
'> "catalogId": 1,
'> "catalogName": "MySQL Magazine Catalog",
'> "edition": "First edition"
'> }'
-> );
Query OK, 1 row affected (0.03 sec)The second schema validation function is called JSON_SCHEMA_VALIDATION_REPORT(). It takes validation further by generating a report of the validation result.
To show an example, use the same schema as before. Use the following user-defined variable for the JSON document to validate:
mysql> SET @document = '{
'> "catalogId": 1,
'> "catalogName": "MySQL Magazine Catalog",
'> "edition": "First edition"
'> }';
Query OK, 0 rows affected (0.00 sec)The function returns a JSON document as a report:
mysql> SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);
+---------------------------------------------------+
| JSON_SCHEMA_VALIDATION_REPORT(@schema, @document) |
+---------------------------------------------------+
| {"valid": true} |
+---------------------------------------------------+
1 row in set (0.00 sec)The validation report could be especially useful in debugging a JSON document. This is because it includes a reason why the document is not valid. Using the same schema, test the validity of a second document. This example document includes a wrong type value in the edition object key. The value is set to an integer. This is when the schema specifies its type as string:
mysql> SET @document = '{
'> "catalogId": 1,
'> "catalogName": "MySQL Magazine Catalog",
'> "edition": 1
'> }';This time, the validation report includes a report explaining why the document is not valid.
mysql> SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_SCHEMA_VALIDATION_REPORT(@schema, @document) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"valid": false, "reason": "The JSON document location '#/edition' failed requirement 'type' at JSON Schema location '#/properties/edition'", "schema-location": "#/properties/edition", "document-location":"#/edition", "schema-failed-keyword": "type"}|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)How to Specify a Range of Array Values in XPath Expressions
MySQL 8.0.2 added support for value ranges. They use the syntax [M to N] to specify a range of values in an XPath expression. This feature is useful in functions that search and modify values, such as the JSON_EXTRACT() and JSON_SET() functions. For example, use the JSON_EXTRACT() function to get JSON array elements. Start at index 0 and end at index 2 in the example array:
SELECT JSON_EXTRACT('[1, true, "c", 4, null]', '$[0 to 2]');
+------------------------------------------------------+
| JSON_EXTRACT('[1, true, "c", 4, null]', '$[0 to 2]') |
+------------------------------------------------------+
| [1, true, "c"] |
+------------------------------------------------------+You may specify the rightmost array element using the synonym last. The following example specifies an [M to N] range for values using the synonym last.
SELECT JSON_EXTRACT('[1, true, "c", 4, null]', '$[last-2 to last]');
+--------------------------------------------------------------+
| JSON_EXTRACT('[1, true, "c", 4, null]', '$[last-2 to last]') |
+--------------------------------------------------------------+
| ["c", 4, null] |
+--------------------------------------------------------------+Conclusion
You have learned about the new JSON-related functions in MySQL 8.x. Now, it is your turn to start using them. The examples show how to use the new functions. By understanding them, you can use the functions in your JavaScript applications. The applications can access the MySQL database via Node.js, a JavaScript runtime. You can experiment with the new JSON functions using the CoderPad’s MySQL Online IDE & Code Editor. Get up to speed with the latest JSON specification, the ECMA-404 - Ecma International.
For further learning, check out the official documentation for:
Gain control over your UX
See how users are using your site as if you were sitting next to them, learn and iterate faster with OpenReplay. — the open-source session replay tool for developers. Self-host it in minutes, and have complete control over your customer data. Check our GitHub repo and join the thousands of developers in our community.
