How do you index one specific key inside a JSONB column?
Create a B-tree expression index on the extracted value, such as (data ->> 'email'), and query with the same expression. This is smaller and faster than a GIN index for equality or range on one known key.
Explain it simply
A GIN index on a whole JSONB document records every key and value, which is flexible but large. If your app only ever searches by data ->> 'email', it is wasteful to index everything. Index just that one extracted value, exactly like indexing a normal column.
Example
CREATE INDEX users_email_idx ON users ((profile ->> 'email'));
-- uses the index
SELECT * FROM users WHERE profile ->> 'email' = 'asha@example.com';
-- numeric key: cast inside the index, and use the same cast in queries
CREATE INDEX users_age_idx ON users (((profile ->> 'age')::int));
SELECT * FROM users WHERE (profile ->> 'age')::int BETWEEN 20 AND 25;
-- not used: different expression
SELECT * FROM users WHERE profile -> 'email' = '"asha@example.com"';Note the double parentheses: the outer pair is index syntax, the inner pair makes it an expression.
If a key is queried this often and has a fixed type, consider promoting it to a real column, possibly a generated column GENERATED ALWAYS AS (profile ->> 'email') STORED, which gives statistics, constraints and simpler SQL.
What to say in the interview
"For one known key I index the expression, with the same expression and cast in the query. GIN is for ad hoc containment across many keys. If a key matters this much, I move it to a proper or generated column."
Common mistakes
- Missing the double parentheses, which causes a syntax error.