The JSON_ARRAY_INSERT function in MySQL is used to insert one or more values into a specified position within a JSON array. This function is particularly useful when working with JSON data in MySQL, allowing you to modify and update JSON arrays easily, enabling the insertion of new data points without altering the entire document.

Syntax

The syntax for the JSON_ARRAY_INSERT function is as follows:

JSON_ARRAY_INSERT(json_array, [path], value[, ...])

json_array: The JSON array where the values will be inserted.
path: Optional. The path within the JSON array where the new values will be inserted. If not specified, the values will be inserted at the beginning of the array.
value: The value(s) to be inserted into the JSON array.

Example

Now, let’s look at an example to illustrate the usage of the JSON_ARRAY_INSERT function:

Suppose you have a table named employee with a JSON column named skills containing an array of skills for each employee. Here is a sample table:

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    skills JSON
);

INSERT INTO employee VALUES
(1, 'John Doe', '[ "Java", "SQL", "JavaScript" ]'),
(2, 'Jane Smith', '[ "Python", "HTML", "CSS" ]');

Now, let’s say you want to insert a new skill, “Node.js,” into John Doe’s skills at the second position. You can use the JSON_ARRAY_INSERT function for this:

UPDATE employee
SET skills = JSON_ARRAY_INSERT(skills, '$[1]', 'Node.js')
WHERE id = 1;

In this example:

‘$[1]’ is the path indicating the second position in the JSON array.
‘Node.js’ is the value to be inserted.

After running this query, John Doe’s skills array will be updated to include “Node.js” at the second position:

SELECT skills FROM employee WHERE id = 1;

Output: ["Java", "Node.js", "SQL", "JavaScript"]

This demonstrates how the JSON_ARRAY_INSERT function allows you to insert values into specific positions within a JSON array, providing flexibility when working with JSON data in MySQL.