Working with JSON_INSERT() function in MySQL 8

Overview

The JSON_INSERT() function in MySQL is a powerful tool that allows you to insert new values into a JSON document without replacing existing values. In this tutorial, we’ll explore how to use the JSON_INSERT() function effectively, with examples ranging from basic to advanced scenarios.

Understanding JSON_INSERT()

The JSON_INSERT() function is part of the comprehensive JSON support provided by MySQL 8. This function is used to add a new key-value pair to a JSON object, or a new value to an array at a specific index, as long as the key or index does not exist. If the key or index exists, the function will not replace the existing value, ensuring that no data is inadvertently overwritten.

Basic Usage of JSON_INSERT()

To understand the basic usage of JSON_INSERT(), let’s consider a simple example. Suppose you have a JSON column in your database table, and you want to insert a new field into your JSON document.

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

This query will return the following JSON object:

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

Note that JSON_INSERT() has three parameters: the JSON document, the path to insert the new value, and the value to be inserted.

Inserting Multiple Values

JSON_INSERT() can also be used to insert multiple values at once by providing additional path-value pairs. Let’s enhance the previous example:

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

The result JSON object will be:

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

Inserting Values into a JSON Array

If you want to insert a value into a JSON array without overwriting existing elements, you can specify the appropriate array index using a zero-based position. Consider this example:

SELECT JSON_INSERT('["a", "b"]', '$[2]', 'c');

This will yield:

[
    "a",
    "b",
    "c"
]

However, if you attempt to insert at an index that already exists, the function will not perform the insertion, as showcased below:

SELECT JSON_INSERT('["a", "b"]', '$[1]', 'c');

Since index 1 in the array already has a value (‘b’), the output is:

[
    "a",
    "b"
]

Advanced Usage: Working with Nested JSON Objects

JSON_INSERT() can also handle nested JSON objects and arrays. If you have a more complex structure, you can specify deeper paths to insert your values:

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

The JSON output for this query will look like:

{
    "a": {
        "b": 1,
        "c": "new"
    }
}

Here, a new key-value pair (‘c’:’new’) is inserted into the nested JSON object under the key ‘a’.

Conditional Insertions

In scenarios where you only want to perform the insertion under certain conditions, you can use the JSON_EXISTS() function alongside JSON_INSERT() to ensure the desired path does not already exist:

SELECT IF( JSON_EXISTS(json_data, '$.newKey'), json_data, JSON_INSERT(json_data, '$.newKey', 'value'))
FROM some_table;

This statement first checks if ‘newKey’ exists in json_data (retrieved from some_table). If it does not exist, it inserts the new key-value pair, otherwise it returns the original JSON data.

Common Mistakes and Considerations

Some frequent mistakes and considerations to keep in mind while using the JSON_INSERT() function are:

• The ‘$’ symbol is used to denote the root of the JSON document in path expressions. Always start your path expressions with ‘$’.
• Index-based path expressions for arrays must be zero-based integers, denoted by square brackets ‘[]’.
• Be aware of the MySQL strict mode setting, which can affect how errors in JSON functions are handled.

Performance Tips

Working with JSON data in MySQL is convenient, but it might not always be the most performant option depending on the circumstances. Here are some quick tips to improve performance:

• Use JSON functions only when necessary, and try to normalize your data when possible.
• Create generated columns to index parts of your JSON document if you frequently query those parts.

Conclusion

Throughout this tutorial, we’ve explored and practiced various applications of the JSON_INSERT() function in MySQL 8. As we’ve seen, JSON_INSERT() is an invaluable tool for safely updating JSON data by adding new elements without overwriting existing ones. By understanding and leveraging the functions’ capabilities, you can greatly enhance your applications’ interactions with JSON data stored in MySQL databases.