SQL Reference ยท JSON and Special Features
SQL json_set(), json_insert() and json_remove() functions
Reference for modifying JSON in SQL: json_set, json_insert, json_replace, json_remove and json_patch, how each treats existing paths, plus dialect equivalents.
JSON functions never change a document in place. They take a JSON value and return a new one with your changes applied; you then SELECT it or store it back with UPDATE. The functions differ in what they do when a path already exists or is missing.
json_set() overwrites or creates, json_insert() only creates, json_replace() only overwrites, json_remove() deletes, and json_patch() merges one document into another using the JSON Merge Patch rules.
SELECT json_set('{"plan": "free"}', '$.plan', 'pro', '$.seats', 5); -- {"plan":"pro","seats":5}
Try it
Press Run (or Ctrl + Enter) to see the result here.
json_insert() leaves plan as free but adds seats. json_replace() changes plan and ignores the missing seats. json_patch() deletes keys whose patch value is null.
Press Run (or Ctrl + Enter) to see the result here.
$.tags[#] means "one past the last element", so json_set() appends. Removing $.tags[0] shifts later elements down.
Syntax
json_set(json, path, value [, path, value ...]) json_insert(json, path, value [, path, value ...]) json_replace(json, path, value [, path, value ...]) json_remove(json, path [, path ...]) json_patch(target, patch)
| Function | Path exists | Path missing |
|---|---|---|
json_set |
overwrites | creates |
json_insert |
keeps the old value | creates |
json_replace |
overwrites | does nothing |
json_remove |
removes | does nothing |
json_patch |
merges (null deletes the key) |
adds |
Returns: the modified JSON as text. Arguments are applied left to right, so later pairs see earlier changes. A value that is plain text becomes a JSON string; pass json('...') to insert JSON (booleans, objects, arrays). If the input is not valid JSON, an error is raised.
Dialect differences
| Database | Overwrite or create | Create only | Overwrite only | Remove |
|---|---|---|---|---|
| SQLite | json_set |
json_insert |
json_replace |
json_remove |
| MySQL | JSON_SET |
JSON_INSERT |
JSON_REPLACE |
JSON_REMOVE |
| PostgreSQL | jsonb_set(doc, '{a,b}', val) |
jsonb_insert |
jsonb_set(..., create_missing := false) |
doc - 'key', #- |
| SQL Server | JSON_MODIFY(doc, '$.a', val) |
same function | same function | JSON_MODIFY(doc, '$.a', NULL) |
| Oracle | JSON_TRANSFORM(doc, SET '$.a' = val) |
INSERT |
REPLACE |
REMOVE |
-- PostgreSQL: path is a text array, value must be jsonb UPDATE profiles SET settings = jsonb_set(settings, '{notifications}', 'false'::jsonb); -- MySQL UPDATE profiles SET settings = JSON_SET(settings, '$.notifications', FALSE); -- SQL Server UPDATE profiles SET settings = JSON_MODIFY(settings, '$.theme', 'dark');
Usage notes
- Return value, not side effect. Calling
json_set(settings, ...)in aSELECTchanges nothing in the table. - Lost updates.
UPDATE ... SET settings = json_set(settings, ...)is safe for one key at a time in a single statement. Reading the document in application code and writing it back can overwrite someone else's change. - Embedded JSON vs text.
json_set(x, '$.a', '[1,2]')stores the string"[1,2]"; usejson('[1,2]'). - Missing parents. In SQLite,
json_set('{"x":1}', '$.a.b', 5)creates the parent too and returns{"x":1,"a":{"b":5}}. PostgreSQL'sjsonb_set()only creates the last key, so the parent object must already exist; set the whole parent object instead. - Cost. Each change rewrites the whole document; for heavily updated data, use real columns or a child table.
- Removal shifts arrays. Remove from the highest index first when deleting several elements.