MySQL provides us with a bunch of functions that can modify JSON values. Here are ten functions that we can use to modify JSON values in MySQL.

The JSON_ARRAY_APPEND() Function

The JSON_ARRAY_APPEND() function appends values to the end of the given array/s within a JSON document and returns the result.

Example:

SELECT JSON_ARRAY_APPEND('[1, 2, 3]', '$', 4);

Result:

[1, 2, 3, 4]

We can add more paths and values as required.

SELECT JSON_ARRAY_APPEND('[1, 2, 3]', '$', 4, '$[1]', 5);

Result:

[1, [2, 5], 3, 4]

The JSON_ARRAY_INSERT() Function

The JSON_ARRAY_INSERT() function inserts values into the given array/s within a JSON document and returns the result.

Example:

SELECT JSON_ARRAY_INSERT('[1, 2, 3]', '$[1]', 4);

Result:

[1, 4, 2, 3]

We can add more paths and values as required.

SELECT JSON_ARRAY_INSERT('[1, 2, 3]', '$[1]', 4, '$[2]', 5);

Result:

[1, 4, 5, 2, 3]

The JSON_INSERT() Function

The JSON_INSERT() function inserts values into the given JSON document and returns the result. This is similar to JSON_ARRAY_INSERT(), but JSON_INSERT() can insert data into more than just arrays.

Example:

SELECT JSON_INSERT('{"a": 1, "b": 2}', '$.c', 3);

Result:

{"a": 1, "b": 2, "c": 3} 

We can add more paths and values as required.

SELECT JSON_INSERT('{"a": 1, "b": 2}', '$.c', 3, '$.d', JSON_ARRAY('Cat', 'Dog'));

Result:

{"a": 1, "b": 2, "c": 3, "d": ["Cat", "Dog"]}

In this example I used the JSON_ARRAY() function to create an array from a list of values.

The JSON_MERGE() Function

The JSON_MERGE() function is a deprecated function that allows us to merge two or more JSON documents.

Example:

SELECT JSON_MERGE( '{"name":"Wag"}', '{"type":"Dog"}' );

Result:

{"name": "Wag", "type": "Dog"}

As mentioned, the JSON_MERGE() function is deprecated. It was deprecated in MySQL 8.0.3, and is subject to removal in a future release of MySQL. That said, it hasn’t yet been removed at the time of writing, and so I’ve included it here.

Fortunately, the JSON_MERGE_PRESERVE() function was added in MySQL 8.0.3 as a synonym for JSON_MERGE(), and it therefore does the same thing as JSON_MERGE().

The JSON_MERGE_PRESERVE() Function

As mentioned, the JSON_MERGE_PRESERVE() function was added in MySQL 8.0.3 as a synonym for the now deprecated JSON_MERGE() function.

So we can replace the previous example with the following:

SELECT JSON_MERGE_PRESERVE( '{"name":"Wag"}', '{"type":"Dog"}' );

Result:

{"name": "Wag", "type": "Dog"}

The JSON_MERGE_PATCH() Function

The JSON_MERGE_PATCH() function performs an RFC 7396 compliant merge of two or more JSON documents and returns the merged result, without preserving members having duplicate keys.

Example:

SELECT JSON_MERGE_PATCH('{"Name": "Bartholomew"}', '{"Name": "Bart"}');

Result:

{"Name": "Bart"}

In this example both documents include a Name key. The JSON_MERGE_PATCH() function chose the value from the second document. This is a different result than we would’ve got if we’d used JSON_MERGE_PRESERVE() or JSON_MERGE().

See JSON_MERGE_PATCH() vs JSON_MERGE_PRESERVE() in MySQL: What’s the Difference? for examples of the difference between these functions.

The JSON_REMOVE() Function

The JSON_REMOVE() function removes data from a JSON document and returns the result.

Example:

SELECT JSON_REMOVE('{"Name": "Homer", "Gender": "Male", "Age": 39}', '$.Age');

Result:

{"Name": "Homer", "Gender": "Male"}

Here, we removed the key/value pair with the key of Age. This is because we specified $.Age as the second argument.

We can remove multiple values by specifying multiple paths, separated by a comma.

The JSON_REPLACE() Function

The JSON_REPLACE() function replaces values in a JSON document and returns the result.

Example:

SELECT JSON_REPLACE('{"a": 1, "b": 2, "c": 3}', '$.b', 9);

Result:

{"a": 1, "b": 9, "c": 3}

In this case, we replaced b‘s value with 9. This is because we specified $.b as the second argument, and 9 as the third.

We can also include multiple paths/values to replace, as required.

The JSON_SET() Function

The JSON_SET() function inserts or updates values in a JSON document and returns the result.

Here’s an example of inserting data:

SELECT JSON_SET('{"a": 1, "b": 2}', '$.c', 3);

Result:

{"a": 1, "b": 2, "c": 3}

In this case, the 3rd argument is inserted into the array at the specified path. I use $.c for the path and 3 for the value to be inserted. This results in a key/value pair of "c": 3.

Here’s an example of updating data:

SELECT JSON_SET('{"a": 1, "b": 2}', '$.b', 3);

Result:

{"a": 1, "b": 3}

The JSON_UNQUOTE() Function

The JSON_UNQUOTE() function unquotes a JSON value and returns the result as a utf8mb4 string.

Example:

SET @data = '"Homer Simpson"';
SELECT 
  @data Original, 
  JSON_UNQUOTE(@data) Unquoted;

Result:

+-----------------+---------------+
| Original        | Unquoted      |
+-----------------+---------------+
| "Homer Simpson" | Homer Simpson |
+-----------------+---------------+