SQLearnAcademy
←Function reference
JSONPostgreSQL only

JSONB_SET

Returns a copy of JSON with a new value at the given path.

Syntax

JSONB_SET(<json>, '{<path>}', <new value>)
JSONB_SET(<json>, '{<path>}', <new value>, <create if missing>)

The path is written as a list of keys in curly braces: '{name}' is a top-level key, '{address,city}' a nested one. The new value must be JSON, so text goes in double quotes: '"Liam"'. If the key doesn't exist, it's added; a fourth argument of false turns that off.

Replace a number
SELECT JSONB_SET('{"name": "Emma", "salary": 1200}', '{salary}', '1300') AS updated;
updated
{"name":"Emma","salary":1300}
Text goes in double quotes
SELECT JSONB_SET('{"name": "Emma"}', '{name}', '"Liam"') AS renamed;
renamed
{"name":"Liam"}
A nested key
SELECT JSONB_SET('{"address": {"city": "London"}}', '{address,city}', '"Berlin"') AS moved;
moved
{"address":{"city":"Berlin"}}

Watch out

The new value is JSON, not text: JSONB_SET(data, '{name}', 'Liam') throws invalid input syntax for type json. Write '"Liam"', and convert a value from a column with TO_JSONB(name).

If the new value is NULL, the whole document becomes NULL, not a key with a null value. For null, pass 'null'.

It doesn't create intermediate keys: JSONB_SET('{}', '{address,city}', '"Berlin"') returns {} unchanged.

It only returns new JSON. To change the data in a table, you need UPDATE … SET data = JSONB_SET(data, …).