Query for Finding an Element in a PostgreSQL JSONB Array

The following is a sample query that you can use to search for rows that have an element in a JSONB array.


CREATE TABLE sample_jsonb (
  id serial PRIMARY KEY,
  name varchar(64),
  json_data jsonb

INSERT INTO sample_jsonb(name, json_data)
  "key": "val1",
  "arr": ["homer", "bart", "barney"]
  "key": "val2",
  "arr": ["marge", "lisa", "maggie"]

select count(*) from sample_jsonb where json_data->'arr' ? 'marge';

Leave a Reply