SQLite json_remove() Function
The SQLite json_remove()
function removes the data specified by a path from a JSON document and returns the modified JSON document.
Syntax
Here is the syntax of the SQLite json_remove()
function:
json_remove(json, path[, path] ...)
Parameters
json
-
Required. A JSON document to remove from.
path
-
Required. A valid path expression.
Return value
The json_remove()
function removes the data specified by path from a JSON document and returns the modified JSON document.
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_remove()
.
Remove elements from a JSON array
The following statement uses json_remove()
to remove two elements with indices 0
and 2
from a JSON array.
SELECT json_remove('[0, 1, 2, [3, 4]]', '$[0]', '$[2]');
json_remove('[0, 1, 2, [3, 4]]', '$[0]', '$[2]')
------------------------------------------------
[1,2]
Here, you may be confused, why use $[2]
to remove the element at index position 3? This is because if there are multiple paths in the parameters list, they json_remove()
executes each path sequentially from left to right. The steps are as follows:
- First, execute
json_remove('[0, 1, 2, [3, 4]]', '$[0]')
, and return a JSON document[1, 2, [3, 4]]
. - Then, execute
json_remove('[1, 2, [3, 4]]', '$[2]')
, and return a JSON document[1, 2]
.
Remove a member from a JSON object
The following statement uses json_remove()
to remove a member from a JSON object.
SELECT json_remove('{"x": 1, "y": 2}', '$.x');
json_remove('{"x": 1, "y": 2}', '$.x')
--------------------------------------
{"y":2}