The SQLite json_set() function allows us to insert into, or replace, a value in a JSON document.

We pass the original JSON as the first argument when we call the function, followed by a path that specifies where to insert/replace the new value, followed by the value to insert/replace.

We can also insert/replace multiple key/value pairs if required.

Syntax

It works like this:

json_set(json, path1, value1, path2, value2...)

Where json represents the original JSON, and path1, value1, path2, value2... are path/value pairs that we can use to insert new values into the JSON document (or replace, as the case may be).

Example

Here’s a basic example to demonstrate:

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

Result:

{"a":1,"b":2}

Here, I inserted a new key/value pair ("b":2) into the JSON document.

We can insert multiple key/value pairs like this:

SELECT json_set('{ "a" : 1 }', '$.b', 2, '$.c', 3 );

Result:

{"a":1,"b":2,"c":3}

I simply added more key/value arguments when I called json_set().

Replace Values

If the key already exists, its value is replaced with the new value:

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

Result:

{"a":1,"b":3}

This is the main difference between json_set() and json_insert(). The json_insert() function won’t insert the value if the key already exists.

Another way to replace values is to use json_replace().

Insert an Object

Here’s an example of inserting a JSON object:

SELECT json_set('{ "a" : 1 }', '$.b', json('{ "c" : 2 }') );

Result:

{"a":1,"b":{"c":2}}

In this case, I used the json() function to return my argument as a JSON string. Here’s what happens when I don’t do that:

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

Result:

{"a":1,"b":"{ \"c\" : 2 }"}

The JSON document is inserted as a text value instead of a JSON object, and its double quotes are therefore escaped with backslashes.

However, simply removing the single quotes results in an error:

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

Result:

Parse error: unrecognized token: "{"
  SELECT json_set('{ "a" : 1 }', '$.b', { "c" : 2 } );
                          error here ---^

Without single quotes or the json() function, we get an error as soon as it encounters the left curly brace.

Another way to insert a JSON object is to use the json_object() function instead of the json() function:

SELECT json_set('{ "a" : 1 }', '$.b', json_object('c', 2) );

Result:

{"a":1,"b":{"c":2}}

Insert an Array

It’s a similar thing when inserting arrays:

SELECT json_set('{ "a" : 1 }', '$.b', json('[ 2, 3, 4 ]'));

Result:

{"a":1,"b":[2,3,4]}

If we remove the json() function, we get this:

SELECT json_set('{ "a" : 1 }', '$.b', '[ 2, 3, 4 ]');

Result:

{"a":1,"b":"[ 2, 3, 4 ]"}

And if we remove the single quotes, we get an error:

SELECT json_set('{ "a" : 1 }', '$.b', [ 2, 3, 4 ]);

Result:

Parse error: no such column:  2, 3, 4 
  SELECT json_set('{ "a" : 1 }', '$.b', [ 2, 3, 4 ]);
                          error here ---^

We can alternatively use the json_array() function instead of json(). That function allows you to create an array based on its arguments:

SELECT json_set('{ "a" : 1 }', '$.b', json_array( 2, 3, 4 ) );

Result:

{"a":1,"b":[2,3,4]}

Append Values to the end of an Array

We can use json_set() to append values to the end of an array.

To do this, use an array index of [#]:

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

Result:

[1,2,3,4]

The same principle applies to nested arrays:

SELECT json_set('[ 1, [ "a", "b" ], 3 ]', '$[1][#]', "c" );

Result:

[1,["a","b","c"],3]

Replace Array Elements

We can use json_set() to replace elements in the array:

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

Result:

[1,4,3]

Arrays are zero based, and so [1] indicates the second array item.

The json_replace() function can also be used to replace existing elements. Again, this is different to json_insert(), which doesn’t replace existing elements.

Invalid Paths

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

SELECT json_set('{ "a" : 1 }', 'b', 2);

Result:

Runtime error: JSON path error near 'b'

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_set('{ "a" : 1', '$.b', 2);

Result:

Runtime error: malformed JSON

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