SQLite json_set(): Update or Add JSON Values

SQLite json_set() updates an existing JSON path or adds a missing one. It returns the modified document as JSON text.

Syntax

json_set(json, path, value [, path2, value2, ...])

The first argument is a JSON document. Each path starts with $, such as $.name or $[0]. You can supply multiple path/value pairs, applied from left to right. Malformed JSON or an invalid path causes an error. Use json_valid() to check JSON text before processing it.

Replace a value and add a key

SELECT json_set('{"theme":"light"}', '$.theme', 'dark', '$.notifications', 1);

{"theme":"dark","notifications":1}

Here theme is replaced and notifications is added.

Insert JSON arrays, not quoted strings

SQLite treats ordinary SQL text as a JSON string, even when it looks like an array:

SELECT json_set('{"tags":[]}', '$.tags', '["sql","sqlite"]');

{"tags":"[\"sql\",\"sqlite\"]"}

Use json() to insert an actual JSON array:

SELECT json_set('{"tags":[]}', '$.tags', json('["sql","sqlite"]'));

{"tags":["sql","sqlite"]}

The same distinction applies to JSON objects and booleans: json('true') produces JSON true, while SQL text 'true' produces the JSON string "true".

Append an array element

Use $[#] to append to an existing JSON array:

SELECT json_set('{"tags":["sql"]}', '$.tags[#]', 'sqlite');

{"tags":["sql","sqlite"]}

Compare related functions

A missing intermediate parent is not automatically created in every case. JSON functions are built into SQLite by default starting with 3.38.0; older installations may require JSON1.

Try these examples in the SQLite Playground, which runs SQLite in your browser. See the official SQLite JSON reference for detailed path and value rules.