The SQLite json_insert() function allows us to insert a new value into 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 the new value, followed by the value to insert.

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

Syntax

The function is called like this:

json_extract(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.

Example

Here’s a basic example to demonstrate:

SELECT json_insert('{ "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_insert('{ "a" : 1 }', '$.b', 2, '$.c', 3 );

Result:

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

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

Key/Value Already Exists?

The json_insert() function won’t insert the value if the key already exists:

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

Result:

{"a":1}

In this case, the key a already exists in the JSON, and so trying to insert a new value for that key doesn’t work.

To replace a value, use either json_replace() or json_set().

Insert an Object

Here’s an example of inserting another JSON document:

SELECT json_insert('{ "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_insert('{ "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.

This is fair enough though. After all, we enclosed the new value in single quotes. How was SQLite to know whether we wanted to insert a string or a JSON object?

And it’s not as if we can simply remove those single quotes:

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

Result:

Parse error: unrecognized token: "{"
  SELECT json_insert('{ "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_insert('{ "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_insert('{ "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_insert('{ "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_insert('{ "a" : 1 }', '$.b', [ 2, 3, 4 ]);

Result:

Parse error: no such column:  2, 3, 4 
  SELECT json_insert('{ "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_insert('{ "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_insert() to append values to the end of an array.

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

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

Result:

[1,2,3,4]

The same principle applies to nested arrays:

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

Result:

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

Invalid Paths

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

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

Result:

Runtime error: malformed JSON

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