PostgreSQL: How to Replace Values in JSONB Field

Often in PostgreSQL you may want to replace specific values in a JSONB field.

You can use the replace() function with the following syntax to do so:

SELECT replace(player_info::text, 'Guard', 'Gd')::jsonb AS player_info
FROM athletes;

This particular example will replace each occurrence of the value Guard with Gd in the JSONB column named player_info of the athletes table.

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

Example: How to Replace Values 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, '{"role":["Guard", "Forward", "Center"]}');
INSERT INTO athletes VALUES (0002, '{"role":["Guard", "Forward", "Center"]}');
INSERT INTO athletes VALUES (0003, '{"role":["Guard", "Guard", "Center"]}');
INSERT INTO athletes VALUES (0004, '{"role":["Guard", "Forward", "Forward"]}');

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

 id |                player_info                
----+-------------------------------------------
  1 | {"role": ["Guard", "Forward", "Center"]}
  2 | {"role": ["Guard", "Forward", "Center"]}
  3 | {"role": ["Guard", "Guard", "Center"]}
  4 | {"role": ["Guard", "Forward", "Forward"]}

The player_info column contains information in a JSON format that shows the role of various athletes on each basketball team.

Suppose that we would like to replace each occurrence of the value Guard with Gd in each array.

We can use the following syntax to do so:

SELECT replace(player_info::text, 'Guard', 'Gd')::jsonb AS player_info
FROM athletes;

              player_info               
----------------------------------------
 {"role": ["Gd", "Forward", "Center"]}
 {"role": ["Gd", "Forward", "Center"]}
 {"role": ["Gd", "Gd", "Center"]}
 {"role": ["Gd", "Forward", "Forward"]}

Notice that each occurrence of Guard has been replaced with Gd in each array in the player_info column.

You can use similar syntax to replace specific elements in a JSONB array in your own table in PostgreSQL.

Additional Resources

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

PostgreSQL: How to Filter Table by JSONB Field
PostgreSQL: How to Find the Size of an Array
PostgreSQL: How to Check if Array is Empty