MySQL JSON_ARRAY_INSERT() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_ARRAY_INSERT() function to insert elements into a specified position in a JSON array.

Introduction to MySQL JSON_ARRAY_INSERT() function

The JSON_ARRAY_INSERT() function is used to insert one or more elements into a specified position in a JSON array of a JSON document.

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

JSON_ARRAY_INSERT(json_doc, path1, value1 [ path2, value2] ...)Code language: SQL (Structured Query Language) (sql)

In this syntax:

json_doc: This is the JSON document that contains the JSON array into which you want to insert elements.
path: The path to the array within the JSON document.
value: This is the value that you want to insert into the JSON array.

The JSON_ARRAY_INSERT() function returns the modified JSON document. It returns NULL if any argument is NULL.

If the json_doc is not a valid JSON document or the path is not a valid path expression, the function will issue an error.

If you have multiple paths, the function evaluates them from left to right, with each pair building upon the result of the previous operation, ultimately returning the updated JSON array.

MySQL JSON_ARRAY_INSERT() function examples

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

1) Inserting elements into a JSON array

The following example uses the JSON_ARRAY_INSERT() function to insert elements into a JSON array:

SELECT JSON_ARRAY_INSERT('["red","green"]', '$[1]', 'blue');Code language: SQL (Structured Query Language) (sql)

Output:

+------------------------------------------------------+
| JSON_ARRAY_INSERT('["red","green"]', '$[1]', 'blue') |
+------------------------------------------------------+
| ["red", "blue", "green"]                             |
+------------------------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this example, we insert the ‘blue’ string into the second position specified by the path $[1] of the JSON array '["red", "green"]'. The function returns the modified JSON document with the updated elements.

2) Inserting elements into a JSON array of a JSON document stored in a table

First, create a new table called styles:

CREATE TABLE styles(
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON
);Code language: SQL (Structured Query Language) (sql)

Next, insert a new row into the styles table:

INSERT INTO styles(data)
VALUES('{ "colors": ["red", "green"], "fonts": ["serif"] }');Code language: SQL (Structured Query Language) (sql)

Then, retrieve data from the styles table:

SELECT * FROM styles
WHERE id = 1;Code language: SQL (Structured Query Language) (sql)

Output:

+----+--------------------------------------------------+
| id | data                                             |
+----+--------------------------------------------------+
|  1 | {"fonts": ["serif"], "colors": ["red", "green"]} |
+----+--------------------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

After that, insert the 'blue' string into the second position of the colors array using the JSON_ARRAY_INSERT() function:

UPDATE styles
SET data = JSON_ARRAY_INSERT(data,'$.colors[1]', 'blue')
WHERE id = 1;Code language: SQL (Structured Query Language) (sql)

In this query, the path '$.colors[1]' means:

$ is the current JSON document.
colors: is the array into which you want to insert elements.
colors[1]: This specifies the second positon in the array to insert the new element.

Finally, retrieve the data from the styles table again to observe the change:

SELECT * FROM styles
WHERE id = 1;Code language: SQL (Structured Query Language) (sql)

Output:

+----+----------------------------------------------------------+
| id | data                                                     |
+----+----------------------------------------------------------+
|  1 | {"fonts": ["serif"], "colors": ["red", "blue", "green"]} |
+----+----------------------------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

Use the JSON_ARRAY_INSERT() function to insert elements into a specified position in a JSON array within a JSON document.

Was this tutorial helpful?