json
jsonb_set
Postgres function that returns a copy of target with the value at path replaced by new_value. Creates the key if create_missing (default true); paths use text arrays like '{users,0,name}'.
jsonb_set(target, path text[], new_value jsonb [, create_missing])Parameters / Modifiers
| Parameter | Purpose |
|---|---|
| create_missing | If false, do nothing when the path does not already exist |
| jsonb_insert | Sibling function that inserts without overwriting existing keys |
| || operator | Shallow merge of two jsonb values (top-level only) |
Examples
SELECT jsonb_set('{"a":1}'::jsonb, '{a}', '2');Returns {"a": 2}
UPDATE users SET data = jsonb_set(data, '{prefs,theme}', '"dark"');Nested key update, quotes required because the new value is JSON
SELECT jsonb_set('{}'::jsonb, '{a,b}', '1', true);Returns {"a": {"b": 1}} — auto-creates the parent object
SELECT jsonb_set('[10,20,30]'::jsonb, '{1}', '99');Array element by index — returns [10, 99, 30]
Dialect notes / Gotcha
Postgres-only, and only on jsonb (not json). The new_value must itself be jsonb — string literals need extra quotes ('"dark"'::jsonb).
Related functions
jsonb_agg
Postgres aggregate that collects rows into a jsonb array. Its sibling jsonb_object_agg builds a jsonb object from key/value pairs.
-> ->> #> #>> (JSON accessors)
Postgres JSON navigation operators. Single-arrow -> and ->> take one key or index; hash-arrows #> and #>> take a text-array path for deep access. The double-arrow forms (->>, #>>) always return text; the single-arrow forms return jsonb.
COALESCE
Returns the first non-NULL argument, or NULL if every argument is NULL. Short-circuit evaluated — later expressions are not computed once a non-NULL value is found.
← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet