What does the @> operator do on JSONB and why is it preferred for filtering?
a @> b is true when jsonb a contains jsonb b, meaning every key and value in b appears in a. It can use a GIN index, whereas filtering with ->> text comparisons usually cannot.
Explain it simply
Containment asks "does this big document include everything in this small pattern?". The pattern {"type":"login"} matches any event document with at least that key and value, however many other fields it has.
Nested objects and arrays work too: arrays match if they contain the listed elements, in any order.
Example
CREATE TABLE events (id bigint, data jsonb);
INSERT INTO events VALUES
(1, '{"type":"login","user":{"id":7},"tags":["web","mobile"]}'),
(2, '{"type":"logout","user":{"id":7}}');
SELECT id FROM events WHERE data @> '{"type":"login"}'; -- 1
SELECT id FROM events WHERE data @> '{"user":{"id":7}}'; -- 1, 2
SELECT id FROM events WHERE data @> '{"tags":["mobile"]}'; -- 1
CREATE INDEX events_data_gin ON events USING gin (data);
-- or the smaller jsonb_path_ops variant, which supports @> only
CREATE INDEX events_data_gin2 ON events USING gin (data jsonb_path_ops);By contrast, WHERE data ->> 'type' = 'login' is not served by that GIN index. For that style of query, build a B-tree expression index on (data ->> 'type').
What to say in the interview
"@> checks whether one JSONB contains another and is what a GIN index accelerates. For equality on one known key, a B-tree expression index on (data ->> 'key') is better. jsonb_path_ops makes the GIN index smaller."
Common mistakes
- Creating a GIN index and then filtering with ->>, which cannot use it.