-- Orders where any line item has quantity > 10
SELECT * FROM orders
WHERE payload @? '$.items[*] ? (@.quantity > 10)';

Indexing: Pick the Operator Class to Match Your Queries

-- Default GIN — supports @>, ?, ?|, ?&
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- jsonb_path_ops — supports @> ONLY, but smaller & faster
CREATE INDEX idx_events_payload_pathops
ON events USING GIN (payload jsonb_path_ops);

Per the PostgreSQL JSON Indexing docs: if every query is a containment check, use jsonb_path_ops. If you also need bare key-existence (?), you need the default jsonb_ops.

Expression index for a single hot field (cheaper than a full GIN index if that's all you filter on):

CREATE INDEX idx_events_status ON events ((payload ->> 'status'));

-- Must match exactly — this uses the index:
SELECT * FROM events WHERE payload ->> 'status' = 'failed';

Generated column — promote a field to a real, typed, indexable column without giving up the document:

ALTER TABLE orders
ADD COLUMN customer_email TEXT
GENERATED ALWAYS AS (payload ->> 'customer_email') STORED;

CREATE INDEX idx_orders_customer_email ON orders (customer_email);

Always verify the planner is actually using what you built:

EXPLAIN ANALYZE
SELECT * FROM events WHERE payload @> '{"status": "failed"}';
-- Look for Bitmap Index Scan / Index Scan on your GIN index, not Seq Scan

Optimization Notes That Actually Move the Needle

• GIN write cost: every insert generates multiple index entries. Postgres batches them in a pending list rather than merging immediately — tune gin_pending_list_limit if you see merge-related latency spikes during peak writes.
• TOAST: large JSONB values get compressed and stored out-of-line. Updating even one key in a TOASTed document rewrites the whole value — split frequently-updated fields into their own column if documents are large.
• Don't SELECT payload if you need two fields:

  SELECT payload ->> 'status', payload ->> 'user_id' FROM events WHERE id = 1;

• Structural guardrails without full normalization:

  ALTER TABLE events ADD CONSTRAINT payload_has_status CHECK (payload ? 'status');

A Production-Ready Table Pattern

CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    event_type TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    payload JSONB NOT NULL,
    status TEXT GENERATED ALWAYS AS (payload ->> 'status') STORED
);

CREATE INDEX idx_events_payload ON events USING GIN (payload jsonb_path_ops);
CREATE INDEX idx_events_status ON events (status);
CREATE INDEX idx_events_type_created ON events (event_type, created_at);

Fixed fields stay as real columns. One hot field gets promoted out of the document. Everything genuinely variable stays in payload, covered by one GIN index matched to the only operator this table actually needs.

For index type selection beyond JSONB (GiST, BRIN, partial indexes), see Advanced PostgreSQL Indexing: GIN, GiST, BRIN & Partial Indexes Explained. For concurrent updates to the same document, see SELECT FOR UPDATE & Isolation Levels. To validate payload shape during development, the JSON Formatter & Validator and JSON to TypeScript Converter are useful.