skip to content

In PostgreSQL, how do jsonb_set, ||, - and jsonb_insert change part of a jsonb document, and what happens when jsonb_set gets SQL NULL?

level: middleimportance: should knowfreq 45%

answer

  1. functions return a new document
  2. STRICT: NULL in, NULL out
  3. jsonb_set_lax null_value_treatment
  4. || merges only the top level
  5. missing parent path, silent no-op

basics

~20 s

Each 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
sql
-- 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

for a junior

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.

for a middle

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.

for a senior

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.

for a principal

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.