SQLite json_set() Function
The SQLite json_set() function inserts or updates data in a JSON document and return the modified JSON document.
Syntax
Here is the syntax of the SQLite json_set() function:
json_set(json, path, value[, path2, value2] ...)
Parameters
json-
Required. The JSON document to modify.
path-
Required. The path expression where to insert or to be modified.
value-
Required. The new value of
path.
Return value
The json_set() function inserts or updates data in a JSON document and returns the modified JSON document. You can provide multiple pairs of path-value parameters to replace multiple data at once.
If the specified path does not exist in the JSON document, the json_set() function inserts the new value the the path.
If the specified path exists in the JSON document, the json_set() function will replace the value of the path with the new value.
SQLite will give an error if the parameter json is not a valid JSON document. You can use json_valid() verify JSON documents.
Examples
Set some values in a JSON array
Let’s set some new values in the JSON array [1, [2, 3]]:
SELECT json_set('[1, [2, 3]]', '$[0]', 0, '$[2]', 6);
json_set('[1, [2, 3]]', '$[0]', 0, '$[2]', 6)
---------------------------------------------
[0,[2,3],6]Here, the first element of the array is replaced with 0, and 6 is inserted at the end of the array.
Set some values in a JSON object
Let’s modify the JSON object {"x": 1, "y": 1} with the following statement:
SELECT json_set('{"x": 1, "y": 1}', '$.y', 2, '$.z', 3);
json_set('{"x": 1, "y": 1}', '$.y', 2, '$.z', 3)
------------------------------------------------
{"x":1,"y":2,"z":3}