The JSON_INSERT function in MySQL is used to insert a new value into a specified JSON document at a specified path. This function is particularly useful when working with JSON data types in MySQL, allowing you to modify and update JSON documents.

Syntax

Here is the syntax for the JSON_INSERT function:

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

json_doc: The original JSON document where the insertion will take place.
path: The path within the JSON document where the new value should be inserted.
val: The value to be inserted at the specified path.

You can specify multiple path and val pairs to insert multiple values in a single JSON_INSERT function call.

Example

Now, let’s look at an example to illustrate how to use the JSON_INSERT function:

Suppose you have a table named employees with a column named info that stores JSON data. Here’s a simplified table structure:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    info JSON
);

INSERT INTO employees VALUES
(1, '{"name": "John", "age": 30, "department": "IT"}');

Now, let’s say you want to insert a new key-value pair into the JSON document in the info column. You can use JSON_INSERT as follows:

UPDATE employees
SET info = JSON_INSERT(info, '$.salary', 50000)
WHERE id = 1;

In this example:

info: The column containing the original JSON document.
$.salary: The path where the new key-value pair should be inserted.
50000: The value to be inserted at the specified path.

After running this UPDATE statement, the info column for the employee with id = 1 will be updated to include the new key-value pair:

{"name": "John", "age": 30, "department": "IT", "salary": 50000}

This is just a basic example, and you can use JSON_INSERT in more complex scenarios with nested structures and arrays within your JSON documents in MySQL.

In summary, the JSON_INSERT function is a versatile feature for adding new elements to JSON documents within MySQL. It allows for flexible insertion of scalar values, arrays, and objects without modifying the existing structure of the document. This function plays a crucial role in enhancing data manipulation and transformation capabilities in MySQL JSON applications.