Query for Finding the nth Element in a PostgreSQL JSONB Array that Matches a Specified Value

The following is a sample query that you can use to search for rows that have the nth element in a JSONB array that match a specific value.


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"]
  "key": "val2",
  "arr": ["bart", "milhouse", "nelson"]

We now have two records in the database where the string “bart” is contained in the arr array of values. The following query will select the row(s) where the “bart” string is in the 2nd (index 1) element of the array.

FROM sample_jsonb
    (json_data->'arr'->1)::jsonb ? 'bart'

Leave a Reply