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:

  1. First, execute json_remove('[0, 1, 2, [3, 4]]', '$[0]'), and return a JSON document [1, 2, [3, 4]].
  2. 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}