Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT json_set('{"plan": "free"}', '$.plan', 'pro', '$.seats', 5);
-- {"plan":"pro","seats":5}

Try it

SQL The five modifiers side by side

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.

SQL Arrays and UPDATE

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

SQL
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
SQL
-- 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 a SELECT changes 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]"; use json('[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's jsonb_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.