5

Changing Data

MySQL provides many functions for the creation and modification of data. JSON documents can contain objects, arrays, and combinations thereof that can be confusing at first glance. But these functions are powerful and easy to master.

Using Arrays

The JSON standard proclaims that array values shall be of type string, number, object, Boolean, or null. Arrays are very handy for storing multiple values and, unlike objects, they do not need to be in pairs. Remember that arrays are bound by square brackets, [ and ], but objects are bound by curly brackets, { and }.

Because it would be messy to adulterate the countryinfo table, a new database schema and table need to be created. Example 5-1 creates a new schema named testjson, creates a table named y, and inserts some sample data.

Example 5-1 Creating a new database and table using the JSON data type

This new array has three items: $[0] is set to "A", $[1] is set to "B", and $[2] is set to "C". Those accustomed to programming languages that start counting from 1 need to make a mental note because array elements in JSON documents start with 0.

Example 5-2 Data from the new table

Appending Arrays

Arrays may need to be augmented. You can use JSON_ARRAY_APPEND to append values to the end of the designated arrays within a JSON document and then return the result. It will return a NULL if any argument is NULL. The server will report an error if the json_doc argument is not a valid JSON document, any path argument is not a valid path expression, or the expression contains a * or ** wildcard. Note that the path/value pairs are evaluated from the left to the right. The document that is produced by evaluating one pair becomes the new value against which the next pair is evaluated; every new evaluation starts fresh on the latest version of the document that is being processed. And remember that the first element in an array is at $[0], the second is at $[1], and so on. It is easy to append a wildcard (*) to $[0], as shown in Example 5-3.

Example 5-3 Appending the $[0] value

Now $[0] is set to "A", "*". Another way to think about the change made in Example 5-3 is that $[0] is itself a new array within the previous array.

Note that the data has to exist before it can be appended or it will be post-pended.

Example 5-4 Appending data

It is also possible to insert multiple values at once. This is more efficient than sending multiple queries, because each query has to have the user authenticated, syntax checked, query plan generated, and then the query is executed. If possible, you’ll find that it pays to do as much as possible in “one trip” to the server.

Example 5-5 Updating data

Inserting into an Array

JSON_ARRAY_INSERT() is very similar to JSON_ARRAY_APPEND(), but, as the name of the function states, a new value is inserted, instead of appended, at the desired location. It will return a NULL if any argument is NULL. The server will report an error if the json_doc argument is not a valid JSON document, any path argument is not a valid path expression, or the expression contains a * or ** wildcard. Note that the path/value pairs are evaluated from the left to the right. The document that is produced by evaluating one pair becomes the new value against which the next pair is evaluated; every new evaluation starts fresh on the latest version of the document that is being processed.

Example 5-6 Inserting data

The array changes, with $[0] now set to the new value ("&") and the others values shifted down. In addition, multiple inserts can be done at the same time.

Example 5-7 Multiple changes simultaneously made by JSON_ARRAY_INSERT

Again, in general practice, it is best with relational databases to make multiple changes with one query rather than make many small changes with multiple queries.

Using TRUNCATE Before Adding New Data

For the next few examples, is it best that you “wipe the slate clean” and remove the old data by using the TRUNCATE command and then adding new data. For those unfamiliar with the TRUNCATE command, it removes the data but preserves the underlying table structure.

Example 5-8 Cleaning the slate of the old array data and starting fresh with an object

Using JSON_INSERT

You can use JSON_INSERT to insert values into a JSON document. Although it is similar to JSON_SET, JSON_SET is used with keys and values already existing in the document, while JSON_INSERT adds new data. A path/value pair for an already existing path in the document is ignored, and it does not overwrite the existing document value. If a path/value pair does not match a path in the document, it is ignored and has no effect. The server will report an error if the json_doc argument is not a valid JSON document, any path argument is not a valid path expression, or the expression contains a * or ** wildcard. Again note that the path/value pairs are evaluated from the left to the right.

Example 5-9 Using JSON_INSERT

Again, multiple items can be inserted into one statement. The line is read left to right, and after each insert, the next step occurs with the line reexamined including the new element.

Example 5-10 Multiple inserts with JSON_INSERT

Note that in Example 5-10, the query wanted to reset the values for key1 but failed. Why? Because insert is not the same as replace, and JSON_INSERT does not replace an existing value. However the $.key3 information was processed by the server. Although half the query worked as desired, there was no warning or error issued on the half that was not performed. If you use this function, you need to be very careful, because this could lead to major problems later. It would be very easy to presume that key1 has the value of value1x in this case, when it does not—thus database administrators and developers gather gray hairs. Refer to the next section for a possible alternative.

Using JSON_REPLACE

Use JSON_REPLACE for updating existing values in a JSON document. The path/value pair for an existing path in the document overwrites the existing value in the document with the new value. The path/value pair for a path that is nonexistent in the document is ignored and has no effect. The server will report an error if the json_doc argument is not a valid JSON document, any path argument is not a valid path expression, or the expression contains a * or ** wildcard. The path/value pairs are evaluated from the left to the right.

Example 5-11 Using JSON_REPLACE to update values

Note that JSON_REPLACE will not insert a new value:

Example 5-12 JSON_REPLACE will not insert a new value; use JSON_INSERT instead.

JSON_REMOVE

The counterpart to JSON_SET, which is discussed next, is JSON_REMOVE, which is used to delete data from the JSON document. If the element to be removed does not exist in the document, the server does not denote it as an error and it does not affect the document. The server will report an error, however, if the json_doc argument is not a valid JSON document, any path argument is not a valid path expression, or the expression contains a * or ** wildcard. The path/value pairs are evaluated from the left to the right.

Example 5-13 Using JSON_REMOVE to change a document

JSON_REMOVE will remove one or more key/value pairs. When all the key/value pairs need to be removed, it may be easier to use JSON_SET and set the document to a blank or NULL rather than specify each key for the given document.

JSON_SET

The JSON_SET function inserts or updates data in a JSON document and returns the result. If the path/value pair for an existing path is found in the document, the new value will overwrite the old value. But if the path/value pair is nonexistent in the path of the document, it will be added to the document or the member will be added to the object and associated with the new value. If a position value is past the end of an existing array, it will be extended with the new value. The server will report an error if the json_doc argument is not a valid JSON document, any path argument is not a valid path expression, or the expression contains a * or ** wildcard. The path/value pairs are evaluated from the left to the right.

Example 5-14 Using JSON_SET to replace the value of “$.key1” and to add a new value for “$.key99”

JSON_SET will set values for an already defined key (the value of "key1" was changed from "Value 1A" to "Value 1X"). Nonexistent key/values are inserted as directed. Existing keys that are not changed by JSON_SET remain unchanged.

JSON_UNQUOTE

The JSON standards describe how keys and values can be quoted to improve their integrity as they are transferred about. Unfortunately, this protection may not be needed by the downstream function or application and should be stripped. Although the function or application can be engineered to do this, it is often much simpler to have the database do this work. And it can be aliased with the ->> operator.

Example 5-15 JSON_UNQUOTE with JSON_EXTRACT, and aliased with the ->> operator

Whether you use the ->> operator instead of JSON_UNQUOTE with JSON_EXTRACT is a matter of choice, readability, and style. But older versions of MySQL do not have the ->> operator (MySQL 5.7.13 was its introduction).

The Three JSON_MERGE Functions

There are three JSON MERGE functions: JSON_MERGE, JSON_MERGE_ PRESERVE, and JSON_MERGE_PATCH. These are very easy to mistake for one another, but they produce a different effect on the data. To add to any possible confusion, JSON_MERGE is actually a synonym for JSON_MERGE_PRESERVE, which means there are two functions with three names—that is, before JSON_MERGE was deprecated in MySQL version 8.0.3. It will likely be removed in some future release. JSON_MERGE_PRESERVE was introduced in MySQL 5.7.22, and you need to be careful in dealing with earlier versions to avoid confusion and frustration.

So why three separate merge functions? The original JSON_MERGE did not act like similar functions used in programming languages like Python. Feedback from early users was mixed, with some loving the original while others wanted it to match JSON merge functions in their favorite language.

So what does the standard say? Well, not so much in this case. RFC 7159 states that object names should be unique. The implications are that duplicates are not supposed to happen, and the implementation is left up to the developer. So JSON_MERGE_PATCH was created to pair with the original function, now renamed MYSQL_JSON_PRESERVE.

So what are the differences between JSON_MERGE/JSON_MERGE_PRESERVE and JSON_MERGE_PATCH? JSON_MERGE/JSON_MERGE_PRESERVE merges two or more JSON documents and returns the merged result. JSON_MERGE_PATCH merges two or more JSON documents, returns the merged result without preserving members having duplicate keys, and drops any member in the first object whose key is matched in the second object.

Example 5-16 clearly shows the JSON_MERGE/JSON_MERGE_PRESERVE cleaning and merging the two JSON objects while preserving all the values but JSON_MERGE_PATCH, keeping only the latest versions of the key/value pairs after the merge.

Example 5-16 How the various JSON_MERGE functions operate. Be sure to check your version of MySQL to ensure that your query works as desired.

JSON_MERGE

The JSON_MERGE function has undergone a lot of changes since the first version came to light. The original intent was fairly simple, as can be seen in Example 5-17.

Example 5-17 Merging two JSON documents (prior to MySQL 5.7.20)

The two documents had their adjacent keys matched and then their values merged. Depending on the data type, the array or object was combined into one of its data types. A scalar is auto-wrapped as an array and merged as an array. And an adjacent array and object were merged by auto-wrapping the object as an array and merging them as two arrays.

Example 5-18 Using JSON_MERGE to combine data

Notice in Example 5-18 that the keys are sorted!

There was an issue, however, with the “last version wins” order of precedence, which is common in most scripting languages such as PHP’s json_merge function. And this approach would be more consistent with other MySQL JSON functions. So JSON_MERGE will change with the latest release of MySQL. Example 5-18 shows two arrays in the SQL statement SELECT JSON_MERGE('{ "odds" : 1, "evens" : 2 }','{ "odds": 3, "evens" : 4 }'); and with last version wins, Example 5-19 shows the output.

Example 5-19 JSON_MERGE and last version wins precedence (MySQL 8.0.3 and later)

JSON_MERGE_PRESERVE

The JSON_MERGE_PRESERVE function was created to provide the functionality of the original JSON_MERGE where last version wins was not considered. Example 5-17 in the preceding section shows how the JSON_MERGE function performs for MySQL 5.7.

JSON_DEPTH

JSON_DEPTH reports the JSON document’s maximum depth, or a NULL if there is no document. Empty arrays, objects, and scalars will have a depth of 1. An array containing only elements of depth 1 or a nonempty object containing only member values of depth 1 has a depth of 2. Past that, the depth is greater than 2.

Example 5-20 Using JSON_DEPTH

JSON_LENGTH

JSON_LENGTH reports the length of a JSON document or the length of a path if one is provided. It is easy to see where the information comes from when used with the JSON_KEYS function.

Example 5-21 Using JSON_LENGTH

The length of a document is determined as follows: Scalars have a length of 1. Array length is the number of items in the array, and objects are the number of objects in the array. Nested arrays or objects are not counted. In Example 5-22, you can see that the output of JSON_KEYS is the corresponding length of the JSON document.

Example 5-22 Using JSON_LENGTH to investigate a second-level document path

JSON_TYPE

The JSON_TYPE function returns a UTF8MB4 string reporting on the contents of a JSON value—array, object, integer, double, and null.

Example 5-23 Using JSON_TYPE to determine the data type

JSON_VALID

Use JSON_VALID on a JSON document to test for validity before attempting an insertion into the database; this can save you a great deal of time.

Format: JSON_VALID(val)

Example 5-24 Using JSON_VALID to ensure validity of JSON documents

Remember that the MySQL server will reject invalid JSON documents. In Example 5-24 all the expressions are not valid JSON, so the server returns a 0. The first test is a valid JSON object and the third is a valid JSON document. But the second test fails because it is an invalid JSON document.

JSON_STORAGE_SIZE

The JSON_STORAGE_SIZE function reports the size in bytes needed to store the binary representation of the JSON document when it was inserted. This function was introduced in MySQL 5.7.22 and is not found in earlier versions.

Example 5-25 Determining document storage size with JSON_STORAGE_SIZE. The size of a document on disk is roughly 1 gigabyte, but it can be larger while being manipulated in memory.

JSON_STORAGE_FREE

The JSON_STORAGE_FREE function reports the amount of storage space that was freed in bytes in its binary representation after it was updated. The updates need to be in place (not a rewrite of the entire document) using JSON_SET, JSON_REMOVE, or JSON_REPLACE. It will return a 0 if the argument is a JSON document in a string.

Example 5-26 Using JSON_STORAGE_FREE by changing “This is a string”, which is 16 characters, to “a”, which is 1 character, or a net change of 15 bytes