Can my DB?

-- Update a nested field
UPDATE users
SET profile = jsonb_set(profile, '{address,city}', '"New York"')
WHERE id = 1;

-- Merge patch (top-level key merge)
UPDATE users
SET profile = profile || '{"verified": true}'::jsonb
WHERE id = 1;

-- Remove a key
UPDATE users
SET profile = profile - 'legacy_field'
WHERE id = 1;

-- Insert into array
UPDATE users
SET profile = jsonb_insert(profile, '{tags, 0}', '"vip"')
WHERE id = 1;

• jsonb_set(doc, path_array, new_value [, create_missing]) updates or creates a value at the specified path.
• jsonb_insert(doc, path_array, value [, insert_after]) inserts into a JSON array or object.
• The - operator removes a key by name; the #- operator removes by path array.
• The || operator merges two JSONB objects at the top level (RFC 7396-like patch).
• All functions operate on JSONB; use doc::jsonb casts if the column is TEXT.

-- Insert or update a value
UPDATE users
SET profile = JSON_SET(profile, '$.address.city', 'New York')
WHERE id = 1;

-- Insert only (no-op if path exists)
UPDATE users
SET profile = JSON_INSERT(profile, '$.new_field', 'value')
WHERE id = 1;

-- Update only (no-op if path missing)
UPDATE users
SET profile = JSON_REPLACE(profile, '$.name', 'Jane')
WHERE id = 1;

-- Remove a key
UPDATE users
SET profile = JSON_REMOVE(profile, '$.legacy_field')
WHERE id = 1;

• JSON_SET(doc, path, value [, path, value ...]) inserts or updates; multiple path-value pairs may be specified.
• JSON_INSERT inserts only if the path does not already exist.
• JSON_REPLACE updates only if the path already exists.
• JSON_REMOVE(doc, path [, path ...]) removes one or more paths.

UPDATE users
SET profile = JSON_SET(profile, '$.address.city', 'New York')
WHERE id = 1;

• JSON_SET(), JSON_INSERT(), JSON_REPLACE(), JSON_REMOVE() - same semantics as MySQL.
• JSON_MERGE_PATCH(doc, patch) for RFC 7396 merge patch operations.

-- RFC 7396 merge patch (available since 12.2)
UPDATE users
SET profile = JSON_MERGEPATCH(profile, '{"address":{"city":"New York"}}')
WHERE id = 1;

-- JSON_TRANSFORM with multiple operations (21c+)
UPDATE users
SET profile = JSON_TRANSFORM(
  profile,
  SET '$.address.city' = 'New York',
  REMOVE '$.legacy_field'
)
WHERE id = 1;

• JSON_MERGEPATCH(doc, patch) implements RFC 7396 merge patch; available since Oracle 12.2.
• JSON_TRANSFORM (Oracle 21c+) allows multiple typed operations in a single call: SET, INSERT, REPLACE, REMOVE, APPEND, COPY, MOVE, RENAME.
• For older versions, JSON_MERGEPATCH is the only built-in modification function.

-- Update a nested value
UPDATE users
SET profile = JSON_MODIFY(profile, '$.address.city', 'New York')
WHERE id = 1;

-- Remove a key by setting to NULL with JSON_MODIFY
UPDATE users
SET profile = JSON_MODIFY(profile, '$.legacy_field', NULL)
WHERE id = 1;

-- Append to an array
UPDATE users
SET profile = JSON_MODIFY(profile, 'append $.tags', 'vip')
WHERE id = 1;

• JSON_MODIFY(doc, path, new_value) updates a single path; to delete use NULL as the value.
• The 'append $.path' prefix appends to a JSON array.
• No bulk update or merge function; chaining multiple JSON_MODIFY calls is the pattern for multi-path updates.
• Available since SQL Server 2016.

UPDATE users
SET profile = json_set(profile, '$.address.city', 'New York')
WHERE id = 1;

-- Remove a key
UPDATE users
SET profile = json_remove(profile, '$.legacy_field')
WHERE id = 1;

• json_set(), json_insert(), json_replace(), json_remove() added in SQLite 3.38.0.
• Same semantics as MySQL: json_set inserts-or-updates, json_insert inserts-only, json_replace updates-only.
• json_patch(doc, patch) implements RFC 7396 merge patch.

-- Rebuild the JSON using json_parse / json_serialize (SUPER type)
UPDATE users
SET profile = JSON_PARSE(
  JSON_SERIALIZE(JSON_PARSE(profile) || JSON_PARSE('{"verified": true}'))
)
WHERE id = 1;

• No direct path-based JSON modification functions.
• SUPER type supports the || merge operator for semi-structured merge operations.
• For text JSON columns, update via string manipulation or full document replacement is required.

-- Update a field with JSON_UPDATE
UPDATE users
SET profile = JSON_UPDATE(profile, '$set', 'address.city', 'New York')
WHERE id = 1;

-- Merge patch style update
UPDATE users
SET profile = JSON_MERGEPATCH(profile, '{"address":{"city":"New York"}}')
WHERE id = 1;

• Db2 documents JSON_UPDATE for targeted path updates and JSON_MERGEPATCH for patch-style updates.
• Older versions required extracting, modifying, and re-inserting JSON as a string or BSON document.
• JSON function families vary somewhat across Db2 editions and JSON/BSON storage modes.

-- Insert or update a top-level key
UPDATE users
SET profile = OBJECT_INSERT(profile, 'city', 'New York'::VARIANT, TRUE)
WHERE id = 1;

-- Remove a key
UPDATE users
SET profile = OBJECT_DELETE(profile, 'legacy_field')
WHERE id = 1;

-- Merge two objects
UPDATE users
SET profile = OBJECT_INSERT(
  OBJECT_DELETE(profile, 'old_key'),
  'new_key', 'value'::VARIANT
)
WHERE id = 1;

• OBJECT_INSERT(obj, key, value [, update_flag]) inserts or updates a top-level key in a VARIANT object.
• OBJECT_DELETE(obj, key [, key ...]) removes one or more keys.
• For nested paths, the document usually has to be rebuilt from object operations rather than updated directly by path.
• PARSE_JSON() and OBJECT_CONSTRUCT() build new VARIANT documents.

-- Replace or insert a value
UPDATE users
SET profile = JSON_SET(profile, '$.address.city', JSON '"New York"')
WHERE id = 1;

-- Remove a key
UPDATE users
SET profile = JSON_REMOVE(profile, '$.legacy_field')
WHERE id = 1;

-- Append into an array
UPDATE users
SET profile = JSON_ARRAY_APPEND(profile, '$.tags', JSON '"vip"')
WHERE id = 1;

• BigQuery documents JSON_SET, JSON_REMOVE, JSON_ARRAY_APPEND, JSON_ARRAY_INSERT, and JSON_STRIP_NULLS for the native JSON type.
• For STRING columns storing JSON, use PARSE_JSON first or rebuild the document explicitly.
• These functions return a new JSON value; use UPDATE to persist the change.