PostgreSQL: How to Update Single Property Value in JSONB

Often in PostgreSQL you may want to update a single property value in a JSONB column.

You can use the following basic syntax to do so:

UPDATE athletes
SET player_info['team'] = to_jsonb('Lakers'::text)
WHERE id = 6;

This particular example will update the value of the team property in the player_info JSONB column to be ‘Lakers’ only for the row where the value in the id column is equal to 6.

The following example shows how to use this syntax in practice.

Example: Update Single Property Value in JSONB Field in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball teams:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER,
  player_info jsonb
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, '{"team":"Mavs", "points":"25"}');
INSERT INTO athletes VALUES (0002, '{"team":"Celtics", "points":"8"}');
INSERT INTO athletes VALUES (0003, '{"team":"Mavs", "points":"15"}');
INSERT INTO athletes VALUES (0004, '{"team":"Hawks", "points":"30"}');
INSERT INTO athletes VALUES (0005, '{"team":"Kings", "points":"18"}');
INSERT INTO athletes VALUES (0006, '{"team":"Mavs", "points":"24"}');

-- view all rows in table
SELECT * FROM athletes;

 id |            player_info             
----+------------------------------------
  1 | {"team": "Mavs", "points": "25"}
  2 | {"team": "Celtics", "points": "8"}
  3 | {"team": "Mavs", "points": "15"}
  4 | {"team": "Hawks", "points": "30"}
  5 | {"team": "Kings", "points": "18"}
  6 | {"team": "Mavs", "points": "24"}

The player_info column contains information in a JSON format that shows the team name and points scored by various basketball players.

Suppose that we would like to update the value of the team property in the player_info column to be ‘Lakers’ only for the row where the value in the id column is equal to 6.

We can use the following syntax to do so:

-- update 'team' property value where id=6
UPDATE athletes
SET player_info['team'] = to_jsonb('Lakers'::text)
WHERE id = 6;

-- view updated table
SELECT * FROM athletes;

 id |            player_info             
----+------------------------------------
  1 | {"team": "Mavs", "points": "25"}
  2 | {"team": "Celtics", "points": "8"}
  3 | {"team": "Mavs", "points": "15"}
  4 | {"team": "Hawks", "points": "30"}
  5 | {"team": "Kings", "points": "18"}
  6 | {"team": "Lakers", "points": "24"}

Notice that the team property in the last row of the player_info column has been updated to have a value of “Lakers.”

All other values in the column remained unchanged.

Feel free to use similar syntax to update the value of a single property in a JSONB column of your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Extract Key and Value from JSON Objects
PostgreSQL: How to Query Where JSON Column Contains Value
PostgreSQL: How to Export Table to JSON File