The JSONB @> Operator in PostgreSQL: Document Containment and GIN-Index Acceleration

The @> operator asks a JSONB value a single question: "do you contain this fragment?" It is not equality and not field extraction, but a containment test: the left document must include everything described on the right, at any depth. That is more compact than a chain of ->> conditions and, crucially, it can ride a GIN index.

What "contains" means

payload @> '{"plan":"pro"}' is true when payload has a plan key with the value "pro" -- even if dozens of other keys sit next to it. The right operand is a template fragment, not a complete document.

Suppose users has a payload jsonb column holding settings and labels:

SELECT id, email
FROM users
WHERE payload @> '{"plan":"pro"}';

A few rules worth keeping in mind:

• Matching is by subset: the document may carry anything beyond the fragment.
• You can state several conditions at once: payload @> '{"plan":"pro","active":true}' requires both keys.
• Types must match strictly: '{"active":true}' does not match "active":"true" (string versus boolean).
• The <@ operator is the mirror: a <@ b means "a is contained in b."

Nested objects and arrays

The power of @> is that the fragment may be nested, and matching recurses. Put line items and a shipping address inside an order:

SELECT id, amount
FROM orders
WHERE payload @> '{"shipping":{"country":"DE"}}';

It does not matter that shipping also holds city and zip -- it is enough that country equals "DE".

For arrays, @> tests membership, not position or order:

-- order whose items array includes a SKU object
SELECT id
FROM orders
WHERE payload @> '{"items":[{"sku":"A-100"}]}';

-- tags array containing both values, in any order
SELECT id
FROM users
WHERE payload @> '{"tags":["beta","eu"]}';

Gotcha: array scalars match "as a set," and objects inside an array are still compared by containment. So '[1,2]' @> '[2,1]' is true (order is irrelevant), while '[1,2]' @> '[1,2,3]' is false -- the left side has no 3. And remember: an empty object '{}' is contained in every object, so payload @> '{}' is true almost always.

GIN index: where @> truly wins

Without an index, @> forces a sequential scan. But JSONB can sit in a GIN index, and then containment queries get fast:

CREATE INDEX idx_users_payload ON users USING gin (payload);

-- now this can use the index
SELECT id FROM users WHERE payload @> '{"plan":"pro"}';

A detail about operator classes:

• The default (jsonb_ops) supports @>, ?, ?|, ?& and indexes both keys and values.
• The jsonb_path_ops class (USING gin (payload jsonb_path_ops)) is smaller and faster for @>, but it does not support the key-existence operators like ?.

CREATE INDEX idx_users_payload_path
  ON users USING gin (payload jsonb_path_ops);

Verify the plan with EXPLAIN ANALYZE: if you see a Bitmap Index Scan on your GIN index, the planner picked up @>.

@> versus ->> equality

The classic filter extracts a scalar and compares it:

SELECT id FROM users
WHERE payload ->> 'plan' = 'pro';

It works, but there are catches:

• ->> returns text, so numbers and booleans are coerced to strings; a subtle type bug is easy to introduce.
• A B-tree on (payload ->> 'plan') must be built per key separately; @> is served by one GIN index over the whole column.
• For nested fields ->> turns into a ladder payload -> 'shipping' ->> 'country', whereas @> stays a single declaration.

Rule of thumb: to test "the document contains this shape," reach for @> plus GIN. For ranges and inequalities (amount > 100, created_at < ...) @> is useless -- there you need ->> with a cast or a dedicated column.

Other engines lack the operator: in MySQL the analog is the JSON_CONTAINS(payload, '{"plan":"pro"}') function returning 0/1, while path checks go through JSON_EXTRACT/->>. In ClickHouse JSON is usually parsed with functions like JSONExtractString, and there is no single containment operator.