In MySQL, the JSON_ARRAY_INSERT() function is used to insert a new value into an array.

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_ARRAY_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_ARRAY_INSERT('[1, 2, 3]', '$[1]', 4) AS 'Result';

Result:

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

So the 3rd argument is inserted into the array at the specified path. In this case, I use $[1] for the path, which refers to the second element (JSON arrays use zero-based numbering – numbering starts at 0).

Example 2 – Nested Array

Here’s an example of inserting a value into an array that’s nested inside another array.

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

Result:

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

So here, I use $[2][1] as the path. The [2] refers to the 3rd element of the outer array. The [1] refers to the 2nd element of the nested array.

Example 3 – A Larger JSON Document

This example uses a (slightly) larger JSON document. This demonstrates how the path changes depending on where the array is within the document.

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

Result:

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