PostgreSQL: How to Filter Table by JSONB Field

Often in PostgreSQL you may want to filter a table based on specific values in a JSONB field.

You can use the following basic syntax to do so:

SELECT *
FROM athletes
WHERE player_info->>'team' = 'Mavs';

This particular example will filter the athletes table to only show the rows where the key named team in the JSONB column player_info contains the value Mavs.

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

Example: How to Filter Table by 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 filter the table to only show the rows where the key named team contains the value Mavs in the player_info column.

We can use the following syntax to do so:

SELECT *
FROM athletes
WHERE player_info->>'team' = 'Mavs';

 id |           player_info            
----+----------------------------------
  1 | {"team": "Mavs", "points": "25"}
  3 | {"team": "Mavs", "points": "15"}
  6 | {"team": "Mavs", "points": "24"}

Notice that this returns only the rows where the key named team contains the value Mavs in the player_info column.

Feel free to use similar syntax to filter rows based on a JSONB field in your own table in PostgreSQL.

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