In SQLite, the json_patch() function can be used to add, modify, or delete elements of a JSON Object.

To do this, it runs the RFC-7396 MergePatch algorithm to apply a given patch against the given JSON input.

We pass the original JSON as the first argument when we call the function, followed by the patch. The function then applies that patch against JSON in the first argument.

Syntax

The syntax goes like this:

json_patch(T,P)

Where T represents the original JSON, and P is the patch. The function applies patch P against T.

The content of the provided patch P is compared against the current content of the target JSON document T. If P contains members that do not appear in T, those members are added. If T does contain the member, the value is replaced.

Null values in P are given special meaning to indicate the removal of existing values in T.

Examples

Here are some examples to demonstrate.

Insert

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

Result:

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

Trying to insert a new member with a null value doesn’t work:

SELECT json_patch('{ "name" : "Fluffy" }', '{ "age" : null }');

Result:

{"name":"Fluffy"}

Null values are actually used for removing members from the JSON (as seen in a later example).

Update

SELECT json_patch('{ "name" : "Fluffy" }', '{ "name" : "Baldy" }');

Result:

{"name":"Baldy"}

If there are multiple key/value pairs, but we only want to update one, we only need to specify that one in our second argument:

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

Result:

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

The same concept applies when updating multiple key/value pairs – we only need to specify those ones:

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

Result:

{"name":"Baldy","type":"Cat","age":11}

Update & Insert

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

Result:

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

Delete/Remove

Null values in the merge patch are given special meaning to indicate the removal of existing values in the target:

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

Result:

{"name":"Fluffy"}

Arrays

The json_patch() function cannot append elements to an array, nor modify individual elements of an array. It can only insert, replace, or delete the whole array as a single unit.

So, here’s an example of appending an element to an array:

SELECT json_patch('[ 1, 2, 3 ]', '[ 1, 2, 3, 4 ]');

Result:

[1,2,3,4]

I had to replace the original array with a whole new one. So technically, I didn’t actually append anything – I simply replaced the whole array with another one.

The same concept applies if the array is within an object:

SELECT json_patch('{ "scores" : [ 1, 2, 3 ] }', '{ "scores" : [ 1, 2, 3, 4 ] }');

Result:

{"scores":[1,2,3,4]}

If you need to work with arrays, try functions such as json_set(), json_insert(), json_remove() and json_replace() instead.

Replace an Object with an Array

We can replace objects with an array by simply supplying an array as the patch:

SELECT json_patch('{ "name" : "Fluffy", "age" : 10 }', '[ "Fluffy", 10 ]');

Result:

["Fluffy",10]

Replace an Array with an Object

And it works the other way around too:

SELECT json_patch('[ "Fluffy", 10 ]', '{ "name" : "Fluffy", "age" : 10 }');

Result:

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