In PostgreSQL, how do jsonb_set, ||, - and jsonb_insert change part of a jsonb document, and what happens when jsonb_set gets SQL NULL?
answer
- functions return a new document
- STRICT: NULL in, NULL out
- jsonb_set_lax null_value_treatment
- || merges only the top level
- missing parent path, silent no-op
basics
~20 sEach returns a new document: jsonb_set replaces or adds the value at a path, || merges top-level keys, - removes keys or elements, and jsonb_insert inserts. jsonb_set is STRICT, so a SQL NULL new value makes the whole result NULL.
solid answer
~50 s`jsonb_set(answers, '{respondent,country}', '"FR"')` replaces the value at a path, or adds it when only the last step is missing (`create_if_missing` defaults to true); if an earlier step is missing it returns the document unchanged, silently. `||` merges two objects with the right side winning, but only at the top level: a nested object on the right replaces the whole nested object. `-` deletes a key, a `text[]` of keys or an array element by index, and `#-` deletes along a path. `jsonb_insert` inserts into an array before the element at the path (after it with `insert_after` true) and raises `cannot replace existing key` for an object key that exists. The trap: `jsonb_set` is STRICT, so a NULL new value returns NULL and the UPDATE nulls the whole column. `jsonb_set_lax` defaults to storing JSON null instead. In PostgreSQL 18, `jsonb_strip_nulls` gained `strip_in_arrays`.
code
sql · 9 lines-- Null-safe update of one answer
UPDATE form_responses
SET answers = jsonb_set_lax(answers, '{nps}', to_jsonb($1::int), true, 'delete_key')
WHERE id = $2;
-- Rename a key, touching only rows that have it
UPDATE form_responses
SET answers = (answers - 'email') || jsonb_build_object('contact_email', answers->'email')
WHERE answers ? 'email';go deeper
Recall that jsonb_set replaces a value at a path, || merges and - removes a key, and that each returns a new document rather than editing the old one in place.
Explain create_if_missing, the shallow merge of ||, jsonb_insert's before and after rules, and why a STRICT jsonb_set turns a NULL parameter into a NULL document.
Guard production updates with jsonb_set_lax or COALESCE, NOT NULL on the column, WHERE clauses that skip untouched rows and batched backfills, knowing every change rewrites the whole document.
Treat keys that different writers update often and independently as a layout signal: they belong in their own columns or rows rather than inside one shared document.
## Every change builds a new document A `jsonb` value is never edited in place. The functions and operators that change a document return a **new** document, and an `UPDATE` stores that new value in a new row version. There is no partial update of one key on disk, however small the change. ## The modification toolbox | Tool | What it does | Rule to remember | |---|---|---| | `jsonb_set(target, path, new_value, create_if_missing)` | replaces the value at `path`, or adds it if only the last step is missing | earlier steps must exist, or `target` comes back unchanged; `create_if_missing` defaults to true | | `jsonb_set_lax(..., null_value_treatment)` | like `jsonb_set`, but defines what a SQL NULL `new_value` means | default `'use_json_null'`; also `'delete_key'`, `'return_target'`, `'raise_exception'` | | `jsonb_insert(target, path, new_value, insert_after)` | inserts into an array before (or after) the element at `path`; adds an object key only if absent | an existing key raises `cannot replace existing key` | | `a \|\| b` | for two objects, the union of keys with `b` winning; for arrays, all elements | top level only, not recursive | | `a - 'key'`, `a - '{k1,k2}'::text[]`, `a - 2` | deletes a key, several keys or matching string elements, or an array element by index | an integer index on a non-array raises an error | | `a #- '{path}'` | deletes the field or element at a path | path elements are keys or array indexes | | `jsonb_strip_nulls(target, strip_in_arrays)` | removes object fields whose value is JSON null, recursively | `strip_in_arrays` (new in 18, default false) also removes null array elements | | `SET answers['k1']['k2'] = '...'` | subscript assignment in an UPDATE | creates missing intermediate objects; a NULL column is treated as empty | ## The SQL NULL trap `jsonb_set`, `jsonb_insert` and `jsonb_strip_nulls` are declared **STRICT**: if any argument is SQL NULL, the function is not called and the result is NULL. Application code often builds the new value from a parameter: ```sql UPDATE form_responses SET answers = jsonb_set(answers, '{nps}', to_jsonb($1::int)) WHERE id = $2; ``` When `$1` is NULL, `to_jsonb(NULL::int)` is NULL, `jsonb_set` returns NULL, and the statement replaces the respondent's whole document with SQL NULL. On a column declared `NOT NULL` the statement fails with a not-null violation instead, which is the better outcome. The fixes: 1. Use `jsonb_set_lax`, which is not strict: by default it stores JSON `null` at the path; `'delete_key'` removes the key; `'raise_exception'` turns the mistake into an error. 2. Wrap the value: `COALESCE(to_jsonb($1::int), 'null'::jsonb)`. 3. Declare the column `NOT NULL` so a NULL document can never be written silently. ## Path rules that surprise - **Missing parents are a silent no-op** for `jsonb_set`: `jsonb_set('{"a": 1}', '{b,c}', '2')` returns `{"a": 1}`. Subscript assignment behaves differently: `SET answers['b']['c'] = '2'` turns `{"a": 1}` into `{"a": 1, "b": {"c": 2}}`, but raises an error if an intermediate value is a scalar. - **Shallow merge**: `settings || '{"theme": {"color": "blue"}}'` replaces the entire `theme` object, dropping sibling keys such as `font`. To change one nested key, use `jsonb_set` with the full path. - **The new value must be `jsonb`**: a string is written `'"FR"'`, JSON quotes inside SQL quotes, or produced with `to_jsonb('FR'::text)`. - **Paths are `text[]`**: array positions are written as numbers in the path, `'{ratings,0,score}'`, and negative numbers count from the end. ## jsonb_strip_nulls in PostgreSQL 18 `jsonb_strip_nulls` removes object fields whose value is JSON `null`, recursively, but leaves `null` array elements alone unless `strip_in_arrays`, added in 18, is true. Releases 18.0 to 18.2 mistakenly marked it STABLE instead of IMMUTABLE, which blocked its use in index expressions; 18.3 corrected the marking for newly initialised or upgraded clusters, and the release notes give a catalog update for clusters created before that. ## Cost Each statement above writes a complete new copy of the document into a new row version. For a small document that is trivial; for a large document stored out of line, the whole value is compressed and stored again, which is one reason to keep frequently updated values out of big documents.
- Two sessions run UPDATE ... SET answers = jsonb_set(answers, ...) on the same row, each changing a different key. Under READ COMMITTED, are both changes kept?Yes. The second UPDATE waits for the first one's row lock, then re-evaluates its `SET` expression against the newly committed row version, so its `jsonb_set` applies to a document that already has the first change. Both survive. They would not if the application read the document, changed it in memory and wrote the whole value back: then the last writer wins.
- When is subscript assignment preferable to jsonb_set?When missing parent objects should be created: `SET answers['respondent']['country'] = '"FR"'` builds `respondent` if it is absent and treats a NULL column as an empty object, while `jsonb_set` silently returns the document unchanged. It raises an error if an intermediate value is a scalar, so prefer `jsonb_set` when a missing parent should be a quiet no-op.
- jsonb_set with a NULL new value stores a JSON null at that path
- The || operator merges nested objects recursively
- jsonb_set creates any missing parent objects along the path
- jsonb_insert overwrites an object key that already exists
- Updating one key writes only that key, not the whole document
Assumes PostgreSQL 18; jsonb_strip_nulls gained strip_in_arrays in 18, and 18.3 restored its IMMUTABLE marking for new clusters.