PostgreSQL: How to Remove Element from JSONB Array

Often in PostgreSQL you may want to remove a specific element from a JSONB array.

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

UPDATE athletes
SET player_info = 
    jsonb_set(player_info, '{role}', (player_info->'role') - 'Forward');

This particular example will remove each element with the value Forward in the array named role of the player_info column in the athletes table.

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

Example: How to Remove Element from JSONB Array 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 name of the role of various athletes on each basketball team.

Suppose that we would like to remove the value Forward from each array.

We can use the following syntax to do so:

-- remove 'Forward' from each array
UPDATE athletes
SET player_info = 
    jsonb_set(player_info, '{role}', (player_info->'role') - 'Forward');

-- view updated table
SELECT * FROM athletes;

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

Notice that Forward has been removed from each array in the player_info column.

Note: If the value ‘Forward’ occurs multiple times then each occurrence is removed from the array.

Feel free to use similar syntax to remove specific elements from 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