MySQL JSON_INSERT() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_INSERT() function to insert new elements to a JSON document.

Introduction to MySQL JSON_INSERT() function

The JSON_INSERT() function allows you to add one or more elements to a JSON document.

Here’s the syntax of the JSON_INSERT() function:

JSON_INSERT(json_doc, path, value[, path, value] ...)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• json_doc: This is the JSON document you want to insert data into.
• path: The JSON path that specifies the position in the json_doc where you want to insert the new value.
• value: The value you want to insert.

Note that you can insert multiple values at the positions specified by paths in a single function call.

The JSON_INSERT() function processes the path/value pair one by one, starting from the left. It evaluates the first pair and uses the updated JSON document for evaluating the next pair till to the last pair.

If the path exists in the JSON document, the function ignores and doesn’t replace the current value in the JSON document.

If the path doesn’t exist in the JSON document, the JSON_INSERT() function will:

• Add the key/value to an object if the member is not present in an existing object.
• Extend an array with a new value if the path specifies an element after the last element of an existing array. If the existing value is not an array, the function wraps it as an array and then extends it with a new value.

Otherwise, the function will also ignore the nonexisting path in the document.

The JSON_INSERT() function returns the new document with the values inserted into the json_doc at the specified paths.

The function returns NULL if any argument is NULL. Also, it issues an error if the json_doc is not a valid JSON document or if the path is not a valid path expression, or if one of the paths contains an asterisk (*) or double asterisk (**) wildcard.

MySQL JSON_INSERT() function examples

Let’s take some examples of using the JSON_INSERT() function.

1) Inserting a new value into an existing object

The following example uses the JSON_INSERT() function to insert a new key/value pair into an object in a JSON document:

SELECT 
  JSON_INSERT(
    '{"name": "John", "age": 30}', '$.job', 
    'MySQL Expert'
  ) AS updated_json;Code language: SQL (Structured Query Language) (sql)

Output:

+----------------------------------------------------+
| updated_json                                       |
+----------------------------------------------------+
| {"age": 30, "job": "MySQL Expert", "name": "John"} |
+----------------------------------------------------+
1 row in set (0.02 sec)Code language: SQL (Structured Query Language) (sql)

In this example, we inserted the key “job” with the value “MySQL Expert” into an existing object of the JSON document.

2) Inserting into nested objects

The following example uses the JSON_INSERT() function to insert values into nested objects within a JSON document:

SELECT 
  JSON_INSERT(
    '{"person": {"name": "Alice", "address": {"city": "Los Angeles"}}}', 
    '$.person.address.zip', '90001'
  ) AS updated_json;Code language: SQL (Structured Query Language) (sql)

In this example, we have an object with the key person and a nested object with the key address.

We use the JSON_INSERT() function to insert the key zip with the value 90001 into the address object.

3) Inserting into arrays

The following example uses the JSON_INSERT() function to add a value to a JSON array:

SELECT 
  JSON_INSERT(
    '{"skills":["MySQL","PHP","JavaScript"]}', 
    '$.skills[3]', 
     'React'
  ) AS updated_json;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------------------------------------------+
| updated_json                                        |
+-----------------------------------------------------+
| {"skills": ["MySQL", "PHP", "JavaScript", "React"]} |
+-----------------------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The reason we can add a value because the path $.skills[3] does not exist in the document.

If you specify a path that exists in the document, the JSON_INSERT() function ignores it like this:

SELECT 
  JSON_INSERT(
    '{"skills":["MySQL","PHP","JavaScript"]}', 
    '$.skills[1]', 
     'React'
  ) AS result;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------------------------------+
| result                                     |
+--------------------------------------------+
| {"skills": ["MySQL", "PHP", "JavaScript"]} |
+--------------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The JSON document doesn’t change.

4) Inserting multiple values

The following example uses the JSON_INSERT() function to insert two new values into an existing JSON object:

SELECT 
  JSON_INSERT(
    '{"name": "John", "age": 25}', '$.city', 
    'San Jose', '$.country', 'USA'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------------------------------------------------------+
| result                                                            |
+-------------------------------------------------------------------+
| {"age": 25, "city": "San Jose", "name": "John", "country": "USA"} |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the JSON_INSERT() function to insert one or more values into a JSON document specified at specified paths.