In MySQL, the JSON_INSERT() function inserts a value into a JSON document and returns the result.

You provide the JSON document as the first argument, followed by the path to insert into, followed by the value to insert.

Syntax

The syntax goes like this:

JSON_INSERT(json_doc, path, val[, path, val] ...)

Where:

• json_doc is the JSON document.
• path is the path of the element for which to insert the new value at.
• val is the new value that will be inserted.

The path-value pairs are evaluated left to right. The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.

Example 1 – Basic Usage

Here’s an example to demonstrate.

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

Result:

+--------------------------+
| Result                   |
+--------------------------+
| {"a": 1, "b": 2, "c": 3} |
+--------------------------+

So the 3rd argument is inserted into the array at the specified path. In this case, 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 another example:

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

Result:

+------------------------------------------------+
| Result                                         |
+------------------------------------------------+
| {"Age": 39, "Name": "Homer", "Gender": "Male"} |
+------------------------------------------------+

Example 2 – Trying to Overwrite an Existing Path

A path-value pair for an existing path in the document is ignored and does not overwrite the existing document value.

So if we modify the previous example to insert a value at Name (which already exists), no insertion takes place:

SELECT 
  JSON_INSERT('{"Name": "Homer", "Gender": "Male"}', '$.Name', "Bart") AS 'Result';

Result:

+-------------------------------------+
| Result                              |
+-------------------------------------+
| {"Name": "Homer", "Gender": "Male"} |
+-------------------------------------+

Example 3 – Arrays

You can insert values into an array by using a path past the end of the existing array. Example:

SELECT JSON_INSERT('[1, 2, 3]', '$[3]', 4) AS 'Result';

Result:

+--------------+
| Result       |
+--------------+
| [1, 2, 3, 4] |
+--------------+

However, if you use a path that already exists in the array, the array will remain unchanged:

SELECT JSON_INSERT('[1, 2, 3]', '$[1]', 4) AS 'Result';

Result:

+-----------+
| Result    |
+-----------+
| [1, 2, 3] |
+-----------+

If the existing value is not an array, it is autowrapped as an array, then extended with the new value. Example:

SELECT JSON_INSERT('{"a": 1, "b": 2}', '$[2]', 3) AS 'Result';

Result:

+-----------------------+
| Result                |
+-----------------------+
| [{"a": 1, "b": 2}, 3] |
+-----------------------+

But once again, if you try to insert a value at a path that already exists, the JSON document remains unchanged:

SELECT JSON_INSERT('{"a": 1, "b": 2}', '$[0]', 3) AS 'Result';

Result:

+------------------+
| Result           |
+------------------+
| {"a": 1, "b": 2} |
+------------------+

Example 4 – A Larger JSON Document

Here’s an example with a (slightly) larger JSON document.

SET @data = '{  
    "Person": {    
       "Name": "Homer", 
       "Hobbies": ["Eating", "Sleeping"]  
    }
 }';
SELECT JSON_INSERT(@data, '$.Person.Hobbies[2]', "Base Jumping") AS 'Result';

Result:

+----------------------------------------------------------------------------------+
| Result                                                                           |
+----------------------------------------------------------------------------------+
| {"Person": {"Name": "Homer", "Hobbies": ["Eating", "Sleeping", "Base Jumping"]}} |
+----------------------------------------------------------------------------------+

Also see JSON_ARRAY_INSERT() and JSON_ARRAY_APPEND() if you need to insert or append data into a JSON array.