PostgreSQL: Query Where JSON Column Contains Value

Often in PostgreSQL you may want to query for rows where a JSON column contains a specific value.

You can use the following basic syntax to do so:

SELECT *
FROM athletes
WHERE player_info::text LIKE '%Center%';

This particular example will return all rows from the athletes table where the JSON column named player_info contains the value ‘Center’ somewhere in the key-value pair.

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

Example: How to Query Where JSON Column Contains Value 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, '{"Guard":"14", "Center":"25"}');
INSERT INTO athletes VALUES (0002, '{"Guard":"17", "Forward":"8"}');
INSERT INTO athletes VALUES (0003, '{"Guard":"32", "Forward":"15"}');
INSERT INTO athletes VALUES (0004, '{"Guard":"19", "Center":"37"}');

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

 id |           player_info            
----+----------------------------------
  1 | {"Guard": "14", "Center": "25"}
  2 | {"Guard": "17", "Forward": "8"}
  3 | {"Guard": "32", "Forward": "15"}
  4 | {"Guard": "19", "Center": "37"}

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

Suppose that we would like to select all rows that contain the string “Center” in the player_info column.

We can use the following syntax to do so:

SELECT *
FROM athletes
WHERE player_info::text LIKE '%Center%';

 id |           player_info           
----+---------------------------------
  1 | {"Guard": "14", "Center": "25"}
  4 | {"Guard": "19", "Center": "37"}

Notice that this returns only the rows that contain the string “Center” in the player_info column.

Feel free to use similar syntax to select rows in your own table where a JSON column contains a specific value.

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 Select Random Value from Array
PostgreSQL: How to Find the Size of an Array