SQLite provides several functions for inserting, setting, and replacing values in a JSON document. Specifically, it provides json_insert(), json_set(), and json_replace().

These functions perform similar tasks, and you can sometimes use them interchangeably to a certain point.

But there is definitely a clear difference between each function.

The Difference

The following table outlines the difference between these functions:

Function	Overwrite if already exists?	Create if does not exist?
json_insert()	No	Yes
json_replace()	Yes	No
json_set()	Yes	Yes

So the difference between these functions is in how they deal with existent and non-existent keys/values.

Examples

Here are some simple examples to demonstrate how each function deals with existent and non-existent keys/values.

When the Key Already Exists

Here’s how each function deals with updating a key that already exists:

SELECT 
    json_insert('{ "a" : 1 }', '$.a', 2) AS json_insert,
    json_replace('{ "a" : 1 }', '$.a', 2) AS json_replace,
    json_set('{ "a" : 1 }', '$.a', 2) AS json_set;

Result:

+-------------+--------------+----------+
| json_insert | json_replace | json_set |
+-------------+--------------+----------+
| {"a":1}     | {"a":2}      | {"a":2}  |
+-------------+--------------+----------+

We can see that json_insert() didn’t update anything, but the other two functions did.

It’s a similar thing with arrays:

SELECT 
    json_insert('[ 1, 2, 3 ]', '$[1]', 4) AS json_insert,
    json_replace('[ 1, 2, 3 ]', '$[1]', 4) AS json_replace,
    json_set('[ 1, 2, 3 ]', '$[1]', 4) AS json_set;

Result:

+-------------+--------------+----------+
| json_insert | json_replace | json_set |
+-------------+--------------+----------+
| [1,2,3]     | [1,4,3]      | [1,4,3]  |
+-------------+--------------+----------+

When the Key Doesn’t Exist

Here’s what happens when the key doesn’t exist:

SELECT 
    json_insert('{ "a" : 1 }', '$.b', 2) AS json_insert,
    json_replace('{ "a" : 1 }', '$.b', 2) AS json_replace,
    json_set('{ "a" : 1 }', '$.b', 2) AS json_set;

Result:

+---------------+--------------+---------------+
|  json_insert  | json_replace |   json_set    |
+---------------+--------------+---------------+
| {"a":1,"b":2} | {"a":1}      | {"a":1,"b":2} |
+---------------+--------------+---------------+

We can see that json_replace() didn’t insert the new key/value pair, but the other two functions did.

Same deal with arrays:

SELECT 
    json_insert('[ 1, 2, 3 ]', '$[3]', 4) AS json_insert,
    json_replace('[ 1, 2, 3 ]', '$[3]', 4) AS json_replace,
    json_set('[ 1, 2, 3 ]', '$[3]', 4) AS json_set;

Result:

+-------------+--------------+-----------+
| json_insert | json_replace | json_set  |
+-------------+--------------+-----------+
| [1,2,3,4]   | [1,2,3]      | [1,2,3,4] |
+-------------+--------------+-----------+

This can also be done using the [#] path:

SELECT 
    json_insert('[ 1, 2, 3 ]', '$[#]', 4) AS json_insert,
    json_replace('[ 1, 2, 3 ]', '$[#]', 4) AS json_replace,
    json_set('[ 1, 2, 3 ]', '$[#]', 4) AS json_set;

Result:

+-------------+--------------+-----------+
| json_insert | json_replace | json_set  |
+-------------+--------------+-----------+
| [1,2,3,4]   | [1,2,3]      | [1,2,3,4] |
+-------------+--------------+-----------+

One benefit of using [#] is that you don’t need to know how many elements are already in the array.