SQLite json_replace() Function
The SQLite json_replace() function replaces existing data in a JSON document and return modified JSON document.
Syntax
Here is the syntax of the SQLite json_replace() function:
json_replace(json, path, value[, path2, value2] ...)
Parameters
json-
Required. The JSON document to modify.
path-
Required. The path expression where to be modified.
value-
Required. The new value of
path.
Return value
The json_replace() function replaces existing 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.
The json_replace() function can only modify the existing path. If the specified path does not exist in the JSON document, the data will not be inserted.
SQLite will give an error if the parameter json is not a valid JSON document. You can use json_valid() verify JSON documents.
Examples
Replace elements in a JSON array
Let’s replace the first and third elments of [1, [2, 3]] with new values:
SELECT json_replace('[1, [2, 3]]', '$[0]', 0, '$[2]', 6);
json_replace('[1, [2, 3]]', '$[0]', 0, '$[2]', 6)
-------------------------------------------------
[0,[2,3]]Here, we want to replace the first element of the array 0 with and replace the third element of the array with 6.
We found that the first element of the array was successfully replaced by 0. Then, there is no insertion at the end of the array 6. This is because the json_replace() function only replaces data that already exists.
Replace members in a JSON object
Let’s modify the value of y to 2 in the JSON object {"x": 1, "y": 1}:
SELECT json_replace('{"x": 1, "y": 1}', '$.y', 2);
json_replace('{"x": 1, "y": 1}', '$.y', 2)
------------------------------------------
{"x":1,"y":2}