Query JSONB and index it with GIN
Filter on JSONB fields with the containment operator and make it fast with a GIN index.
- SQL
- GreenSnippets
- MIT
CREATE INDEX events_data_gin ON events USING gin (data jsonb_path_ops);
-- Rows whose JSON contains {"type": "signup", "plan": "pro"}
SELECT id, data->>'email' AS email, created_at
FROM events
WHERE data @> '{"type": "signup", "plan": "pro"}'
ORDER BY created_at DESC
LIMIT 50;Example / expected output
-- jsonb_path_ops indexes are smaller and faster for @> but do not support the ? operators.How it works
@> tests whether the left JSONB contains the right one and is the operator a default GIN index accelerates. ->> extracts a field as text for display or ordering. For a single hot key, an expression index on (data->>'status') can be smaller and faster.
This snippet has been reviewed but was not executed by an automated test. Test it in your own environment before relying on it.
Related snippets
- SQL
Top N rows per group
Get the latest or highest N rows for each category using ROW_NUMBER in a subquery.
- SQL
Soft delete with a partial unique index
Keep deleted rows but still enforce unique emails among active rows using a partial unique index.
- SQL
Walk a tree with a recursive CTE
Query an employee/manager or category hierarchy to any depth using WITH RECURSIVE.
- SQL
Pivot rows into columns with FILTER
Turn row values into columns (for example monthly totals) using conditional aggregates, without crosstab.