Adding and Removing Keys From JSONB With jsonb_set and Minus

Documents change shape. A field gets renamed, an optional flag is added, a deprecated key has to go, two objects need merging. PostgreSQL has a small set of operators and functions for this, and each has a case where it quietly does something other than what its name suggests. This article is the reference I keep open when I edit JSONB: what each tool does, the output on small examples, and the places where nothing happens and nothing tells you.

Adding a key

With the concatenation operator

|| combines two JSONB values. For two objects it returns an object with the keys of both, and the right side wins when a key appears on both:

select '{"a": 1}'::jsonb || '{"b": 2, "a": 9}';   -- {"a": 9, "b": 2}

This is the simplest way to set one or several top-level keys, whether or not they exist: attrs || '{"clearance": true, "price": 10}' added clearance and replaced price in one go. The right side can be built from data, as in attrs || jsonb_build_object('reviewed_at', now()).

The limits come from "top level". || does not merge nested objects; it replaces them. Take '{"a":{"x":1}}'::jsonb || '{"a":{"y":2}}'. The result is {"a": {"y": 2}}. The x is gone, because the right side's a replaced the left side's a entirely. On the product table, attrs || '{"specs": {"color": "pink"}}' replaced the whole specs object, and the dimensions and weight were gone, when all I wanted was a new color. If you need a deep merge, write one. This recursive function worked on my test:

create function jsonb_deep_merge(a jsonb, b jsonb) returns jsonb language sql immutable as $$
  select case
    when jsonb_typeof(a) = 'object' and jsonb_typeof(b) = 'object' then
      (select coalesce(jsonb_object_agg(k,
          case when a ? k and b ? k then jsonb_deep_merge(a -> k, b -> k)
               when b ? k then b -> k
               else a -> k end), '{}')
       from (select jsonb_object_keys(a) k union select jsonb_object_keys(b)) keys)
    else b end
$$;

select jsonb_deep_merge('{"a":{"x":1,"z":{"p":1}},"b":1}', '{"a":{"y":2,"z":{"q":2}},"c":3}');
 {"a": {"x": 1, "y": 2, "z": {"p": 1, "q": 2}}, "b": 1, "c": 3}

Arrays are replaced, not merged, in this version, since two arrays under one key are treated as scalars; decide whether you want concatenation, union or replacement for arrays before using it on real data.

On arrays, || appends: '[1,2]'::jsonb || '[3]' is [1, 2, 3], and '[1,2]'::jsonb || '3' is [1, 2, 3] too, while an object concatenated with an array gives an array of two elements, [{"a": 1}, 1]. Check the types on both sides.

With jsonb_set

jsonb_set(target, path, new_value, create_if_missing default true) writes a value at a path. Its behavior on the edges:

jsonb_set('{"a":{"b":1}}', '{a,c}', '2')            {"a": {"b": 1, "c": 2}}   (new last key: created)
jsonb_set('{"a":{"b":1}}', '{a,c}', '2', false)      {"a": {"b": 1}}           (create_if_missing false)
jsonb_set('{"a":1}',       '{b,c}', '2')             {"a": 1}                  (missing parent: nothing)
jsonb_set('{"a":null}',    '{a,b}', '1')             {"a": null}               (parent is not an object)
jsonb_set('{}',            '{a,b,c}', '1')           {}                        (nothing to extend)
jsonb_set('{"a":[1,2,3]}', '{a,1}', '99')            {"a": [1, 99, 3]}         (array position replaced)
jsonb_set('[]',            '{0}', '1')               [1]

The pattern is that jsonb_set creates at most one level, the last key of the path, and only if the object above it exists. When any parent is missing or is not an object, you get the original document back and no error. Code that reports "updated 1 row" while changing nothing is the result. If you need to create a deep path, use the subscript form in PostgreSQL 14 and later (attrs['specs']['coating']['type'] = '"matte"' built the missing coating object), or nest the calls: set {specs,coating} to '{}' first with coalesce, then set the leaf.

The new value must be JSONB, and a SQL NULL there makes the whole result NULL. Updating one key inside a JSONB column covers both, plus jsonb_set_lax, which lets you choose what a SQL NULL means.

Into arrays with jsonb_insert

jsonb_insert(target, path, new_value, insert_after default false) adds an element to an array at a position:

jsonb_insert('{"tags":["a","c"]}', '{tags,1}', '"b"')          {"tags": ["a", "b", "c"]}
jsonb_insert('{"tags":["a","c"]}', '{tags,1}', '"b"', true)    {"tags": ["a", "c", "b"]}

By default the element goes before the position; with insert_after set to true it goes after. To append, either use || (tags || '"d"'), or insert after the last element with {tags,-1}. Applied to an object key that already exists, jsonb_insert('{"a":1}', '{a}', '2') raised cannot replace existing key with a hint to use jsonb_set; on a new key it adds it. That refusal is deliberate and a useful guard: you can use jsonb_insert when overwriting would be a bug.

Removing keys and elements

The minus operator has several forms, and they all return a new document:

'{"a":1,"b":2,"c":3}'::jsonb - 'a'                     {"b": 2, "c": 3}
'{"a":1,"b":2,"c":3}'::jsonb - '{a,c}'::text[]         {"b": 2}
'{"a":1,"b":2}'::jsonb       - 'zzz'                   {"a": 1, "b": 2}     (absent key: no-op)
'["a","b","a","c"]'::jsonb   - 'a'                     ["b", "c"]           (every matching element)
'["a","b","a","c"]'::jsonb   - 1                       ["a", "a", "c"]      (element at index 1)
'["a","b","c"]'::jsonb       - -1                      ["a", "b"]           (last element)
'{"a":[1,2,3]}'::jsonb #- '{a,1}'                       {"a": [1, 3]}        (by path)

On an array, a text operand removes all elements equal to it, an integer operand removes one by position. On an object, only a key matches, so '{"a":"x"}'::jsonb - 'x' removes nothing. The path form, #-, reaches into nested structures. Use attrs #- '{specs,dims,h}' to drop one deep key without rebuilding the parent. Deleting a key that does not exist is not an error, which means a typo in the key name disappears, as with reads.

To keep only certain keys, there is no operator, but a query does it: select jsonb_object_agg(k, v) from jsonb_each(doc) as t(k, v) where k = any (array['a','c']) returned {"a": 1, "c": 3}. To drop JSON nulls, jsonb_strip_nulls(doc) removes every key whose value is null, at all depths, and leaves empty objects behind.

Renaming a key

A rename is a removal plus an addition in a single expression, so that the document is written once:

update products
set attrs = (attrs - 'colour') || jsonb_build_object('color', attrs -> 'colour')
where attrs ? 'colour';

On my tiny test, '{"old":1,"keep":2}'::jsonb - 'old' || jsonb_build_object('new', ...) produced {"new": 1, "keep": 2}. The where attrs ? 'colour' matters twice: it avoids rewriting documents that do not have the key, and it avoids adding "color": null to those that do not. On a large table, do this in id ranges with a commit between batches, since each updated row is a full new copy, as measured in the article above and in working with JSONB in PostgreSQL from first query to index. While the rename is being rolled out, the application has to read both names. That is the real cost of a key rename: the data change is a statement, the compatibility window is a project.

Checking what a change did

Because so many of these calls can be no-ops, verify. After an UPDATE, the row count says how many rows were written, not how many changed. To count real changes, compare before and after in the WHERE clause: where new_doc is distinct from doc, or put the expression in a CTE and filter on inequality. In a script, assert that the key you expected is present: select count(*) from products where not (attrs ? 'color') should be zero after the rename.

A tool such as the JSON diff shows a before and after side by side for a single document, which is easy to run on a handful of rows when you are working out an expression, before you apply it to a table. For the paths in jsonb_set and #-, see extracting nested JSONB values with path operators.

When a key change needs a real migration

The expressions above are fine for a single change. These are the cases where you should stop and plan.

The key is used by running code. Renaming or removing it in the database while old application versions are running breaks them. Deploy readers that accept both, then migrate data, then drop the old path.

The document is large, or there are many rows. Every touched row is rewritten in full, with the index and WAL cost that comes with it. Batch by key range, watch replication lag and let vacuum catch up between batches. If the key only matters to new documents, leave old ones alone and handle the difference at read time.

The change needs a type conversion. Turning "price": "12.50" into "price": 12.5 across a table is a data migration: choose how to treat values that do not convert, and keep the originals until you have verified the result.

You are changing the same field constantly. If a key changes at the rate of a column, it is one. Promote it with a generated column or a real column, as described in the hub guide.

For the full list of operators and functions, see the PostgreSQL manual's tables for JSON processing functions and operators, and for the JSON data model underneath, the JSON types page.