JSON MODIFY / JSON SETJSON
Buy Me A Coffee

Update, insert, or delete values within a JSON document stored in a column, returning the modified JSON. Avoids deserializing the entire document in application code just to change one field.

PostgreSQL function for updating specific paths within a JSONB document, creating a modified copy. Supports creating nested paths with create_missing => true. Useful for JSON document versioning and partial updates.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 5.7.8 Three distinct functions with different insert/update semantics: JSON_SET inserts or replaces (upsert). JSON_INSERT inserts only if the key does not exist. JSON_REPLACE replaces only if the key already exists. JSON_REMOVE deletes a path. All return a new JSON document; the source column is not modified in-place — use UPDATE ... SET col = JSON_SET(col, ...) to persist changes.
PostgreSQL ✓ Supported 9.5 No single JSON_SET function. Instead, the || (concatenation) operator merges/overwrites top-level keys on jsonb. For deep updates, jsonb_set(target, path_array, new_value) (9.5+) modifies a nested key. jsonb_set_lax() (14+) adds NULL-handling options. jsonb_insert() (9.6+) inserts into arrays at a specific position. jsonb_strip_nulls() removes null-valued keys. The #- operator removes a key by path.
SQL Server ✓ Supported 2016 JSON_MODIFY(expression, path, new_value) is the single function for all mutations. Setting new_value to NULL removes the key (or sets it to JSON null — use JSON_MODIFY(col, 'lax $.key', NULL) to force removal). Supports append mode for arrays: JSON_MODIFY(col, 'append $.tags', 'featured'). JSON_MODIFY can be chained/nested to modify multiple paths. Returns NVARCHAR.
Oracle ✓ Supported 21c JSON_TRANSFORM (Oracle 21c+) is the primary function for modifying JSON documents with a dedicated mini-language of operations: SET, INSERT, REPLACE, APPEND, REMOVE, RENAME, KEEP, COPY, MOVE, SORT, FLATTEN. Prior to 21c, JSON modification required extracting values, modifying in application code, and updating the column. Oracle 12c/18c had no in-database JSON mutation functions.
SQLite ✓ Supported 3.9.0 json_set(json, path, value, ...) (upsert), json_insert(json, path, value, ...) (insert only), json_replace(json, path, value, ...) (replace only), and json_remove(json, path, ...) follow the same insert/replace/upsert semantics as MySQL. All available since SQLite 3.9.0. Multiple paths can be modified in a single call. Returns a new JSON string; must be used in an UPDATE to persist.

Details

MySQL and SQLite share the same three-function split (JSON_SET/json_set for upsert, JSON_INSERT/json_insert for insert-only, JSON_REPLACE/json_replace for replace-only) — the most orthogonal API. SQL Server's JSON_MODIFY is a single function that covers all cases including array append, but requires chaining for multiple updates. PostgreSQL has no JSON_SET — it uses the || operator for top-level merges and jsonb_set() for nested updates; the #- operator and - operator handle removal. Oracle had no JSON mutation functions at all until 21c's JSON_TRANSFORM, which introduces a mini-language rather than function arguments. In all engines, these functions return a new JSON value — they do not modify in place; an UPDATE statement is required to persist changes.

Standard Syntax

-- SQL Server: SELECT JSON_MODIFY(data, '$.status', 'active') AS updated FROM users; -- MySQL: SELECT JSON_SET(data, '$.status', 'active') AS updated FROM users; -- PostgreSQL: SELECT data || '{"status": "active"}' AS updated FROM users;

Version Support

MySQL: Since 5.7.8 PostgreSQL: Since 9.5 SQL Server: Since 2016 Oracle: Since 21c SQLite: Since 3.9.0

Per-Database Syntax & Notes

MySQL

Three distinct functions with different insert/update semantics: JSON_SET inserts or replaces (upsert). JSON_INSERT inserts only if the key does not exist. JSON_REPLACE replaces only if the key already exists. JSON_REMOVE deletes a path. All return a new JSON document; the source column is not modified in-place — use UPDATE ... SET col = JSON_SET(col, ...) to persist changes.

-- JSON_SET (upsert a value): UPDATE users SET data = JSON_SET(data, '$.status', 'active') WHERE id = 1; -- JSON_INSERT (add only if key absent): UPDATE users SET data = JSON_INSERT(data, '$.created_at', NOW()) WHERE id = 1; -- JSON_REPLACE (update only if key exists): UPDATE users SET data = JSON_REPLACE(data, '$.status', 'inactive') WHERE id = 1; -- JSON_REMOVE (delete a key): UPDATE users SET data = JSON_REMOVE(data, '$.temp_token') WHERE id = 1; -- Modify nested path: UPDATE users SET data = JSON_SET(data, '$.address.city', 'Portland') WHERE id = 1; -- Append to array: UPDATE posts SET data = JSON_ARRAY_APPEND(data, '$.tags', 'featured') WHERE id = 42;

PostgreSQL

No single JSON_SET function. Instead, the || (concatenation) operator merges/overwrites top-level keys on jsonb. For deep updates, jsonb_set(target, path_array, new_value) (9.5+) modifies a nested key. jsonb_set_lax() (14+) adds NULL-handling options. jsonb_insert() (9.6+) inserts into arrays at a specific position. jsonb_strip_nulls() removes null-valued keys. The #- operator removes a key by path.

-- Top-level key upsert (|| operator): UPDATE users SET data = data || '{"status": "active"}'::jsonb WHERE id = 1; -- Deep nested update (jsonb_set): UPDATE users SET data = jsonb_set(data, '{address,city}', '"Portland"') WHERE id = 1; -- Insert into array at position: UPDATE posts SET data = jsonb_insert(data, '{tags,0}', '"featured"') WHERE id = 42; -- Remove a key (top-level): UPDATE users SET data = data - 'temp_token' WHERE id = 1; -- Remove a nested key (#- operator): UPDATE users SET data = data #- '{address,zip}' WHERE id = 1;

SQL Server

JSON_MODIFY(expression, path, new_value) is the single function for all mutations. Setting new_value to NULL removes the key (or sets it to JSON null — use JSON_MODIFY(col, 'lax $.key', NULL) to force removal). Supports append mode for arrays: JSON_MODIFY(col, 'append $.tags', 'featured'). JSON_MODIFY can be chained/nested to modify multiple paths. Returns NVARCHAR.

-- Set a value: UPDATE users SET data = JSON_MODIFY(data, '$.status', 'active') WHERE id = 1; -- Remove a key (set to SQL NULL, not JSON null): UPDATE users SET data = JSON_MODIFY(data, '$.temp_token', NULL) WHERE id = 1; -- Append to array: UPDATE posts SET data = JSON_MODIFY(data, 'append $.tags', 'featured') WHERE id = 42; -- Chain multiple modifications: UPDATE users SET data = JSON_MODIFY( JSON_MODIFY(data, '$.status', 'active'), '$.updated_at', GETDATE() ) WHERE id = 1; -- Nested path: UPDATE users SET data = JSON_MODIFY(data, '$.address.city', 'Portland') WHERE id = 1;

Oracle

JSON_TRANSFORM (Oracle 21c+) is the primary function for modifying JSON documents with a dedicated mini-language of operations: SET, INSERT, REPLACE, APPEND, REMOVE, RENAME, KEEP, COPY, MOVE, SORT, FLATTEN. Prior to 21c, JSON modification required extracting values, modifying in application code, and updating the column. Oracle 12c/18c had no in-database JSON mutation functions.

-- JSON_TRANSFORM (21c+): UPDATE users SET data = JSON_TRANSFORM( data, SET '$.status' = 'active', SET '$.updated_at' = SYSTIMESTAMP ) WHERE id = 1; -- Remove a key: UPDATE users SET data = JSON_TRANSFORM(data, REMOVE '$.temp_token') WHERE id = 1; -- Append to array: UPDATE posts SET data = JSON_TRANSFORM(data, APPEND '$.tags' = 'featured') WHERE id = 42; -- Pre-21c workaround (extract, modify, reserialize): -- Must be done in application code or a PL/SQL procedure.

SQLite

json_set(json, path, value, ...) (upsert), json_insert(json, path, value, ...) (insert only), json_replace(json, path, value, ...) (replace only), and json_remove(json, path, ...) follow the same insert/replace/upsert semantics as MySQL. All available since SQLite 3.9.0. Multiple paths can be modified in a single call. Returns a new JSON string; must be used in an UPDATE to persist.

-- json_set (upsert): UPDATE users SET data = json_set(data, '$.status', 'active') WHERE id = 1; -- json_remove: UPDATE users SET data = json_remove(data, '$.temp_token') WHERE id = 1; -- Multiple paths in one call: UPDATE users SET data = json_set(data, '$.status', 'active', '$.updated_at', datetime('now') ) WHERE id = 1; -- json_insert (add only if absent): UPDATE users SET data = json_insert(data, '$.created_at', datetime('now')) WHERE id = 1;