We can use the SQLite json_remove() function to remove one or more elements from a JSON object or array.

We pass the original JSON as the first argument when we call the function, followed by one or more paths that specify which elements to remove. By “elements”, I mean either array elements or object members (key/value pairs).

Syntax

The syntax goes like this:

json_remove(X,P,...)

Where X represents the original JSON, and P,... represents the path/s to delete.

Example

Here’s an example to demonstrate:

SELECT json_remove('{ "name" : "Fluffy", "age" : 10 }', '$.age');

Result:

{"name":"Fluffy"}

Here, we specified the $.age path, and that’s what was removed from the JSON document.

Remove Multiple Members

We can remove multiple members from a JSON object by specifying multiple paths, separated by a comma:

SELECT json_remove('{ 
        "name" : "Fluffy",
        "type" : "Cat",
        "age" : 10 
    }', 
    '$.type',
    '$.age'
);

Result:

{"name":"Fluffy"}

Arrays

We can remove array elements by specifying the element’s index:

SELECT json_remove('[ 0, 1, 2, 3 ]', '$[2]');

Result:

[0,1,3]

Arrays are zero-based, and so counting starts from 0.

However, it’s important to be mindful of the order in which the removals are made. Removals occur sequentially from left to right. Changes caused by prior removals can affect the path search for subsequent arguments.

Here’s an example of how that can affect our result:

SELECT json_remove('[ 0, 1, 2, 3 ]', 
    '$[0]',
    '$[2]'
);

Result:

[1,2]

In this case, array element 0 was deleted first, then array element 2 was deleted from the remaining array. In other words, the second path removed the element from the array after the first path had already been removed.

The resulting array is one that we may not have anticipated. Instead of removing elements 0 and 2 from the original array, we’ve removed elements 0 and 3 from the original array.

We can overcome this issue by reordering the paths:

SELECT json_remove('[ 0, 1, 2, 3 ]', 
    '$[2]',
    '$[0]'
);

Result:

[1,3]

No Paths

The path arguments are actually optional. If we call json_remove() with no path arguments, it returns a minified version of the input JSON (i.e. with excess whitespace removed):

SELECT json_remove('{ 
        "name" : "Fluffy",
        "type" : "Cat",
        "age" : 10 
    }'
);

Result:

{"name":"Fluffy","type":"Cat","age":10}

This is the same result we’d get if we used the json() function instead of json_remove().

Non-Existent Path

Specifying a path that doesn’t exist in the input JSON results in the original JSON being returned, unchanged:

SELECT json_remove('{ "name" : "Fluffy", "age" : 10 }', '$.type');

Result:

{"name":"Fluffy","age":10}

Invalid Paths

We’ll get an error if our path is not well-formed:

SELECT json_remove('{ "name" : "Fluffy", "age" : 10 }', 'age');

Result:

Runtime error: JSON path error near 'age'

In this case, I forgot to include $. at the front of the path.

Invalid JSON Documents

We’ll also get an error the JSON is not well-formed:

SELECT json_remove('{ "name" : "Fluffy", "age" }', '$.age');

Result:

Runtime error: malformed JSON

This time the error tells us that our JSON is malformed.