SQLite json_replace() Function

The SQLite json_replace() function replaces existing data in a JSON document and return modified JSON document.


Here is the syntax of the SQLite json_replace() function:

json_replace(json, path, value[, path2, value2] ...)



Required. The JSON document to modify.


Required. The path expression where to be modified.


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.


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)

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)