Update a field inside JSON

-- Postgres: jsonb_set(target, path, new_value [, create_if_missing]).
UPDATE events
SET payload = JSONB_SET(payload, '{user,plan}', '"pro"'::JSONB)
WHERE id = 42;

-- Insert a key that doesn't exist yet (4th argument = TRUE, default).
UPDATE users
SET preferences = JSONB_SET(preferences, '{notifications,email}', 'true'::JSONB);

-- Remove a key.
UPDATE events SET payload = payload - 'temp_field' WHERE id = 42;
UPDATE events SET payload = payload #- '{user,profile,deprecated_field}' WHERE id = 42;

-- MySQL.
UPDATE events SET payload = JSON_SET(payload, '$.user.plan', 'pro') WHERE id = 42;
UPDATE events SET payload = JSON_REMOVE(payload, '$.temp_field') WHERE id = 42;

-- SQL Server.
UPDATE events SET payload = JSON_MODIFY(payload, '$.user.plan', 'pro') WHERE id = 42;

-- Increment a counter inside JSON.
UPDATE event_state
SET payload = JSONB_SET(
  payload,
  '{count}',
  TO_JSONB(((payload ->> 'count')::INT + 1))
)
WHERE id = 1;                                     -- Postgres

-- Merge two JSON objects (right side wins on key conflict).
UPDATE users
SET preferences = preferences || '{"theme":"slate","beta":true}'::JSONB
WHERE id = 42;                                     -- Postgres

-- Conditionally update only if the path exists.
UPDATE events
SET payload = JSONB_SET(payload, '{status}', '"verified"', FALSE)  -- 4th arg FALSE
WHERE payload ? 'status';                                            -- Postgres

-- Remove an item from a JSON array (replace with filtered array).
UPDATE users
SET tags = (SELECT JSONB_AGG(t) FROM JSONB_ARRAY_ELEMENTS(tags) AS t WHERE t::text <> '"old"')
WHERE id = 42;                                     -- Postgres