The SQLite json_replace() function allows us to replace an existing value in a JSON document with another value.

We pass the original JSON as the first argument when we call the function, followed by the path of the value to replace, followed by the value to replace.

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

Syntax

It works like this:

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

Where json represents the original JSON, and path1, value1, path2, value2... are path/value pairs to replace.

Example

Here’s a basic example to demonstrate:

SELECT json_replace('{ "name" : "Fluffy" }', '$.name', "Baldy");

Result:

{"name":"Baldy"}

Here, I updated the value of the name key from Fluffy to Baldy.

We can update multiple key/value pairs like this:

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

Result:

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

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

Here, I used line breaks to make the code easier to read. It could have all been on one line – the result would have been the same.

What if the Key Doesn’t Exist?

If the key doesn’t already exist in the JSON, nothing is replaced:

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

Result:

{"name":"Fluffy"}

This is the main characteristic that distinguishes the json_replace() function from the json_set() and json_insert() functions. Those functions will insert the value if the key doesn’t already exist.

However, it is possible to effectively insert new keys with json_replace() by replacing the whole JSON object/document. Example below.

Replace the Whole JSON Document

We can use json_replace() to replace the whole JSON document with another one:

SELECT json_replace('{ "name" : "Fluffy" }', '$', json('{ "name" : "Baldy" }') );

Result:

{"name":"Baldy"}

So this enables us to effectively insert new keys in the document:

SELECT json_replace('{ 
    "name" : "Fluffy" 
    }', 
    '$', 
    json('{ 
        "name" : "Baldy" ,
        "age" : 11
        }'
    ) 
);

Result:

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

Strictly speaking, we didn’t insert any new keys. We simply replaced the whole document. But the result was a JSON document that contains keys that the original one didn’t contain.

Replace an Embedded Object

We can also replace embedded objects:

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

Result:

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

When I did this, 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_replace('
    { 
        "a" : 1, 
        "b" : { "c" : 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, we can’t simply remove the single quotes, because this results in an error:

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

Result:

Parse error: unrecognized token: "{"
        "b" : { "c" : 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. We therefore need to use single quotes or the json() function, depending on whether we’re inserting a JSON object or a SQL string/text value.

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

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

Result:

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

Replace an Array

The same concept applies with arrays:

SELECT json_replace('{ "a" : [ 1, 2, 3 ] }', '$.a', json('[ 5, 6, 7 ]'));

Result:

{"a":[5,6,7]}

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

SELECT json_replace('{ "a" : [ 1, 2, 3 ] }', '$.a', '[ 5, 6, 7 ]');

Result:

{"a":"[ 5, 6, 7 ]"}

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

SELECT json_replace('{ "a" : [ 1, 2, 3 ] }', '$.a', [ 5, 6, 7 ]);

Result:

Parse error: no such column:  5, 6, 7  (17)
  LECT json_replace('{ "a" : [ 1, 2, 3 ] }', '$.a', [ 5, 6, 7 ]);
                                      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_replace('{ "a" : [ 1, 2, 3 ] }', '$.a', json_array(5, 6, 7));

Result:

{"a":[5,6,7]}

Append Values to the end of an Array

To append values to the end of an array, we can use either the json_insert() or json_set() functions.

However, if we must use json_replace(), we can replace the whole array with another one that has the extra value/s added to the end of the array:

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

Result:

[1,2,3,4]

But as mentioned, json_insert() and json_set() allow you to actually append values to the array without replacing the whole array.

Replace Array Elements

Here’s an example of using json_replace() to replace an element within an array:

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

Result:

[1,4,3]

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

The json_set() function can also be used to replace existing elements. However, the json_insert() function does not allow us to replace existing elements.

Invalid Paths

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

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

Result:

Runtime error: JSON path error near 'a'

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

Result:

Runtime error: malformed JSON

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