Command guide

intermediate json MySQL MariaDB SQL Server PostgreSQL SQLite

Update a Value Inside a JSON Column

Modify a single key in a stored JSON document without replacing the whole object, using `JSON_SET` (MySQL/MariaDB/SQLite), `JSON_MODIFY` (SQL Server), or `jsonb_set` (PostgreSQL).

Created Jun 23, 2026 Last updated Jun 23, 2026 5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Change the theme value inside a JSON settings column

The UPDATE rewrites only the theme key while leaving lang untouched — the stored document becomes {"theme": "dark", "lang": "en"}. In MySQL and MariaDB, JSON_UNQUOTE(JSON_EXTRACT(...)) strips the surrounding quotes from the JSON string value; using the ->> shorthand (config->>'$.theme') is equivalent. In PostgreSQL, config->>'theme' extracts the text value of the key directly (without JSON-literal quotes). The jsonb_set path '{theme}' is a PostgreSQL text array; for a nested key like user.theme use '{user,theme}'. In SQL Server, JSON_MODIFY is also used for arrays: JSON_MODIFY(config, '$.tags[0]', 'urgent') updates the first element. To add a key that does not yet exist, JSON_SET and JSON_MODIFY both insert it automatically; to remove a key, use JSON_REMOVE(config, '$.theme') in MySQL/MariaDB/SQLite or JSON_MODIFY(config, '$.theme', NULL) in SQL Server.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE settings (id INT, config JSON);

INSERT INTO
  settings
VALUES
  (1, '{"theme": "light", "lang": "en"}');

Validated query

SQL

UPDATE settings
SET
  config = JSON_SET (config, '$.theme', 'dark')
WHERE
  id = 1;

SELECT
  JSON_UNQUOTE (JSON_EXTRACT (config, '$.theme')) AS theme
FROM
  settings
WHERE
  id = 1;

Expected result

theme
dark

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE settings (id INT, config NVARCHAR (MAX));

INSERT INTO
  settings
VALUES
  (1, '{"theme": "light", "lang": "en"}');

Validated query

SQL

UPDATE settings
SET
  config = JSON_MODIFY (config, '$.theme', 'dark')
WHERE
  id = 1;

SELECT
  JSON_VALUE (config, '$.theme') AS theme
FROM
  settings
WHERE
  id = 1;

Expected result

theme
dark

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE settings (id INT, config JSONB);

INSERT INTO
  settings
VALUES
  (1, '{"theme": "light", "lang": "en"}');

Validated query

SQL

UPDATE settings
SET
  config = jsonb_set (config, '{theme}', '"dark"')
WHERE
  id = 1;

SELECT
  config - > > 'theme' AS theme
FROM
  settings
WHERE
  id = 1;

Expected result

theme
dark

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE settings (id INT, config TEXT);

INSERT INTO
  settings
VALUES
  (1, '{"theme": "light", "lang": "en"}');

Validated query

SQL

UPDATE settings
SET
  config = json_set (config, '$.theme', 'dark')
WHERE
  id = 1;

SELECT
  json_extract (config, '$.theme') AS theme
FROM
  settings
WHERE
  id = 1;

Expected result

theme
dark

All supported engines return the same result; only the update and extraction syntax differs.

Useful when

Where this command helps.

updating a user's preferred theme stored inside a JSONB settings column without overwriting other preferences
patching a single field in a product metadata JSON column from an automated background job

Explanation

What the command is doing.

When a table stores configuration, metadata, or user preferences in a JSON column, it is often necessary to update a single key without reading the document into application code, modifying it, and writing it back. All major engines provide a function that performs an in-place key update and returns the modified document. MySQL, MariaDB, and SQLite use JSON_SET(column, '$.key', value), which sets the key if it exists and inserts it if it does not. SQL Server uses JSON_MODIFY(column, '$.key', value) with the same semantics. PostgreSQL uses jsonb_set(column, '{key}', 'json_value'::jsonb), where the path is an array literal and the new value must itself be valid JSON — string values must include the quotes, e.g. '"dark"'. To delete a key, pass NULL as the value (MySQL/SQLite/SQL Server) or use the - operator (PostgreSQL: column - 'key'). Nested paths are supported on all engines using dot or array notation: '$.user.theme' or '{user,theme}' in PostgreSQL.

Update a Value Inside a JSON Column

Change the theme value inside a JSON settings column

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.