PostgreSQL 18 reference · JSON Functions

PostgreSQL JSONB_SET() Function

Return jsonb with the value at a path replaced or, by default, created. Combine with || to merge objects.

PostgreSQL 18 Since 9.5

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

jsonb_set(target, path, new_value, create_if_missing)

Parameter	Description
`target`	The original `jsonb`.
`path`	A text array path, such as `'{address,city}'`.
`new_value`	The new `jsonb` value.
`create_if_missing` optional	Add the key if it doesn't exist. Default: `true`

jsonb.

Change a nested value

SELECT jsonb_set('{"name":"Ada","address":{"city":"London"}}', '{address,city}', '"Paris"') AS moved;

Output

                     moved
-----------------------------------------------
 {"name": "Ada", "address": {"city": "Paris"}}
(1 row)

Run this on your computer — see “Get Started” for setup.

Merge with || and remove with -

SELECT '{"theme":"light"}'::jsonb || '{"lang":"en"}' AS merged,
       '{"a":1,"b":2}'::jsonb - 'b' AS removed;

Output

              merged              | removed
----------------------------------+----------
 {"lang": "en", "theme": "light"} | {"a": 1}
(1 row)

Run this on your computer — see “Get Started” for setup.

Version	Change
PostgreSQL 9.5	Added

JSONB_INSERT() · Insert a value into a JSON array at a path (before or after an element) or add a new object key.
JSONB_STRIP_NULLS() · Remove object fields whose value is JSON null, recursively. Since PostgreSQL 18 it can also drop nulls in arrays.
#> and #>> · Read a nested JSON value by a path array: #> returns jsonb and #>> returns text, as in data #>> '{user,name}'.
SQL String Functions · tutorial