SQLite json_insert() Function
The SQLite json_insert() function inserts values into a JSON document and return a new JSON document.
Syntax
Here is the syntax of the SQLite json_insert() function:
json_insert(json, path, value[, path2, value2] ...)
Parameters
json-
Required. The JSON document to modify.
path-
Required. A valid path expression.
value-
Required. The value to the path.
You should specify at least one path and value pair.
Return value
The json_insert() function inserts values into a JSON document and returns a new JSON document. You can provide multiple pairs of path-value parameters to insert multiple values at once.
The json_insert() function can only insert data into paths that do not exist. If the specified path already exists in the JSON document, the data will not be inserted. That is, the json_insert() function does not overwrite existing data.
SQLite will give an error if the parameter json is not a valid JSON document. You can use json_valid() verify JSON documents.
Examples
Here are some examples to show the usages of json_insert().
Insert into JSON array
Let’s insert two elements into the array [1, [2, 3], {"a": [4, 5]}]:
SELECT json_insert('[1, [2, 3]]', '$[0]', 0, '$[2]', 6);
Here, we want to insert 0 at the beginning of the array and 6 at the end of the array. Let’s take a look at the results:
json_insert('[1, [2, 3]]', '$[0]', 0, '$[2]', 6)
------------------------------------------------
[1,[2,3],6]We found that the value 0 was not inserted at the beginning ($[0]) of the array. This is because the array already has a value at the position $[0], so 0 was not inserted. The array has no element at position $[2], so 6 is inserted at the end of the array.
Insert into JSON object
Let’s insert a member y with the value 2 into the JSON object {"x": 1}:
SELECT json_insert('{"x": 1}', '$.y', 2);
json_insert('{"x": 1}', '$.y', 2)
---------------------------------
{"x":1,"y":2}