JSONB Operators and Containment Queries
Use the containment and path operators that GIN indexes can actually accelerate.
Why Operator Choice Decides Index Use
In PostgreSQL, a column of type jsonb can be searched many different ways, but not every operator can use an index. Performance here is almost entirely about choosing operators that a GIN index can accelerate.
- A GIN index (Generalized Inverted Index) stores the keys and values inside your JSON documents so lookups skip the full table.
- The two operators that matter most are containment (
@>) and key existence (?,?|,?&).
This lesson teaches exactly which operators those are, and how to write queries that stay index-friendly.
The Containment Operator @>
The containment operator @> asks: does the left JSONB contain the right JSONB? The right side is a fragment, and Postgres checks that every key/value in it appears in the left document.
'{"a":1,"b":2}' @> '{"a":1}'is true.'{"a":1}' @> '{"a":1,"b":2}'is false (the right side has more).
This is the workhorse for filtering rows: WHERE data @> '{"status":"active"}' finds every row whose JSON includes that pair.
SELECT '{"a":1,"b":2}'::jsonb @> '{"a":1}'::jsonb AS contains_a,
'{"a":1}'::jsonb @> '{"a":1,"b":2}'::jsonb AS contains_both;All lessons in this course
- JSONB Operators and Containment Queries
- GIN vs Expression Indexes on JSONB
- Querying JSONB with JSONPath
- When to Normalize Out of JSONB