Syntax of json_insert()
The syntax of the json_insert() function in SQLite is as follows:
Here, json_string is the existing JSON string in which the new value is to be inserted, the path is the path where the new value is to be inserted, and the value is the new value to be inserted.
How to Use the json_insert() Function in SQLite?
To use the json_insert() function in SQLite, you must first ensure that you have a JSON string that you want to modify. This can be done by creating a table with a column of type JSON, or by using a JSON string literal. When you have your JSON string, you may add new values to it by using the json_insert() method.
To create a table, follow these commands.
id INTEGER PRIMARY KEY AUTOINCREMENT,
data JSON);
Output
Inserting Values Using SQLite json_insert()
Suppose we have a table called students, and it contains a JSON column called data. We want to add new students to this table. We can use the following SQL command:
VALUES (JSON_INSERT('{}', '$.name', 'Jade', '$.age', 22));
INSERT INTO students (data)
VALUES (JSON_INSERT('{}', '$.name', 'Sarah', '$.age', 19));
INSERT INTO students (data)
VALUES (JSON_INSERT('{}', '$.name', 'Emily', '$.age', 21));
INSERT INTO students (data)
VALUES (JSON_INSERT('{}', '$.name', 'Howard', '$.age', 21));
This command will insert new rows into the students table, with a JSON string that looks like this:
Output
Inserting Values into an Existing JSON Object Using SQLite json_insert()
Let’s say we want to add a new value to a JSON object that already exists. We can use the json_insert() function to do this:
SET data = JSON_INSERT(data, '$.email', '[email protected]')
WHERE id = 2;
SELECT * FROM students;
This command will update the JSON string for the student with an ID of 2, inserting a new key-value pair for an email with the value [email protected].
Output
Inserting Objects and Arrays Using SQLite json_insert()
The json_insert() function can also insert objects and arrays into the JSON document. Let’s consider an example where we want to insert an array of programming languages into the JSON document at a specific path.
SET data = json_insert(data, '$.languages', json('["JavaScript", "Python", "Java"]'))
WHERE id = 1;
SELECT * FROM students;
In the above example, we use the json() function to create a JSON array containing the programming languages. The json_insert() function then inserts this array into the JSON document at the ‘$.languages’ path.
Output
Inserting Nested Values Using SQLite json_insert()
The json_insert() function can handle complex JSON structures, including nested objects and arrays. If the specified path doesn’t exist in the JSON document, the function creates the necessary objects and arrays along the path to accommodate the new value. Let’s take a look at a scenario in which we wish to add a nested value to the JSON document.
SET data = json_insert(data, '$.address.city', 'New York')
WHERE id = 1;
SELECT * FROM students;
In the aforementioned example, we provide the value of New York at the $.address.city path.
Output
Conclusion
The json_insert() function in SQLite provides a convenient way to modify JSON data within SQLite tables. It enables the insertion of new values, objects, and arrays into existing JSON documents, allowing for flexible data manipulation. This function proves particularly useful when dealing with complex JSON structures, as it handles nested objects and arrays seamlessly.
