The JSON_ARRAY_APPEND function in MySQL is used to append values to a specified JSON array within a JSON document. This function is part of the JSON functions introduced in MySQL 5.7 for working with JSON data types.

Syntax

Here is the syntax for the JSON_ARRAY_APPEND function:

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

json_doc: The JSON document that contains the target array.
path: The path to the array within the JSON document where you want to append values.
val: The value(s) to be appended to the array.

You can specify multiple pairs of path and val parameters to append multiple values in a single function call.

Example

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

Suppose you have a table named employees with a column named info storing JSON documents, and you want to append a new skill to the “skills” array for a specific employee. Here’s how you can use JSON_ARRAY_APPEND:

-- Sample data setup
CREATE TABLE employees (
    id INT PRIMARY KEY,
    info JSON
);

INSERT INTO employees (id, info) VALUES
(1, '{"name": "John Doe", "skills": ["Java", "SQL"]}'),
(2, '{"name": "Jane Smith", "skills": ["Python", "JavaScript"]}');

-- Append a new skill to the "skills" array for employee with id=1
UPDATE employees
SET info = JSON_ARRAY_APPEND(info, '$.skills', 'C++')
WHERE id = 1;

-- Display the updated information
SELECT * FROM employees;

In this example, the JSON_ARRAY_APPEND function is used in the UPDATE statement. It appends the value ‘C++’ to the “skills” array of the employee with id equal to 1. The $.skills path specifies the location of the array within the JSON document.

After running this query, the “skills” array for the employee with id 1 will now include ‘C++’ in addition to the existing skills.

Keep in mind that the JSON_ARRAY_APPEND function modifies the original JSON document in the specified column. If you need a new modified copy without modifying the original, you can use other functions like JSON_INSERT or JSON_SET.