Complex queries: filters, JOINs, and aggregations over JSONB

Capsule overview

The previous capsules gave you the pieces: operators, path queries, GIN, partials, expression indexes. This capsule shows you how they assemble into real queries — the kind you see in production endpoints, not in docs. You're going to combine WHERE with @>, JOIN with fields extracted from the JSONB, GROUP BY with JSONB-aware aggregations (jsonb_agg, jsonb_object_agg), and ORDER BY with expression indexes that GIN doesn't speed up.

The central theme is: when JSONB gets into queries that also use classic relational SQL, not everything is sped up by a single GIN. You're going to learn to identify which part of the query needs which index — GIN for the search part, expression indexes for JOIN/ORDER BY, partial indexes for recurring hot paths — and you're going to learn the JSONB aggregation functions that real code needs but that rarely show up in tutorials.

By the end you'll be able to write the complete endpoint of the module project: JSONB filters, a JOIN with a relational table, hierarchical aggregations, pagination, all with a validated query plan. It's the capsule that connects "loose technique" with "real application code."

Mental model: think of the query in layers

A serious JSONB query usually has three layers. Each layer picks its appropriate index.

┌───────────────────────────────────────────────────────────────────┐
│                                                                   │
│  Layer 1: FILTERING                                               │
│    WHERE payload @> '...'                                         │
│    → GIN index on payload                                         │
│    → reduces the dataset fast                                     │
│                                                                   │
│  Layer 2: JOIN / ENRICHMENT                                       │
│    JOIN users ON users.id = (payload->>'user_id')::bigint         │
│    → expression index on ((payload->>'user_id')::bigint)          │
│    → resolves relationships                                       │
│                                                                   │
│  Layer 3: AGGREGATION / ORDER / PROJECTION                        │
│    GROUP BY payload->>'country', SUM((payload->>'amount')::num)   │
│    ORDER BY total DESC LIMIT 10                                   │
│    → may need additional expression indexes                       │
│      or early materialization                                     │
│                                                                   │
└───────────────────────────────────────────────────────────────────┘

Mental pattern: when you write a complex JSONB query, identify the three layers. For each one, ask yourself: "which index helps?" If a layer doesn't have an index, you're going to pay a Seq Scan or a sort that ruins performance, even if the others are perfect.

Filtering: combining @> with SQL conditions

The most common way to combine JSONB with SQL is: @> for the JSONB part + an additional SQL condition.

-- The module's events table
SELECT id, payload->>'amount' AS amount
FROM events
WHERE payload @> '{"action": "purchase"}'
  AND created_at >= '2026-04-01'
  AND created_at < '2026-05-01';

What happens:

• WHERE payload @> ... uses GIN. It filters fast.
• WHERE created_at BETWEEN ... uses a B-tree over created_at (assuming one exists).
• The planner combines the two filters with a bitmap AND: each index produces a bitmap, it intersects them, and only reads the rows they have in common.

Validation:

EXPLAIN ANALYZE
SELECT id FROM events
WHERE payload @> '{"action": "purchase"}'
  AND created_at >= '2026-04-01';

-- BitmapAnd
--   ->  Bitmap Index Scan on events_payload_idx
--   ->  Bitmap Index Scan on events_created_at_idx

BitmapAnd is the signature of "the planner combined two indexes." It's typically efficient when both filters are selective.

Filters that are NOT expressible with @>

@> is for exact value matches. It doesn't work for:

• Numeric comparisons (>, <, BETWEEN).
• Pattern matching (LIKE, regex).
• IS NULL or IS NOT NULL on extracted fields.

For those, you combine:

1. What you can with @> (exact value filters).
2. What you can't, with an additional condition (with no dedicated index, or with an expression index).

-- Exact filters (GIN) + numeric comparison (post-filter in memory or expression index)
SELECT id, payload->>'amount' AS amount
FROM events
WHERE payload @> '{"action": "purchase", "country": "US"}'
  AND (payload->>'amount')::numeric > 100;

If the first condition is selective (say, it returns 1000 rows), the additional > 100 over 1000 rows is done in memory without a problem. If the first condition returns 100k rows and the second cuts it to 10k, an expression index over ((payload->>'amount')::numeric) is worth it.

CREATE INDEX events_amount_idx ON events (((payload->>'amount')::numeric));

-- Now the query can use a bitmap AND with both:
EXPLAIN ANALYZE SELECT id FROM events
WHERE payload @> '{"action": "purchase"}'
  AND (payload->>'amount')::numeric > 100;
-- BitmapAnd with events_payload_idx AND events_amount_idx

JOIN: extracting fields from the JSONB to join with relational tables

A very common case: a table with JSONB that has user_id inside, and you want to join with the users table to enrich it.

Setup

DROP TABLE IF EXISTS events, users CASCADE;

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  tier TEXT NOT NULL DEFAULT 'free'
);

INSERT INTO users (name, email, tier)
SELECT
  'user_' || g,
  'user_' || g || '@example.com',
  (ARRAY['free', 'pro', 'enterprise'])[1 + (g % 3)]
FROM generate_series(1, 50000) g;

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO events (payload, created_at)
SELECT
  jsonb_build_object(
    'action', (ARRAY['view', 'click', 'purchase'])[1 + (random() * 2)::int],
    'user_id', (random() * 49999 + 1)::bigint,
    'amount', round((random() * 500)::numeric, 2),
    'country', (ARRAY['US', 'MX', 'ES', 'AR'])[1 + (random() * 3)::int]
  ),
  now() - (random() * interval '90 days')
FROM generate_series(1, 500000);

-- Indexes
CREATE INDEX events_payload_idx ON events USING gin(payload jsonb_path_ops);
CREATE INDEX events_created_at_idx ON events (created_at);
CREATE INDEX events_user_id_idx ON events (((payload->>'user_id')::bigint));

ANALYZE events; ANALYZE users;

The query with a JOIN

-- "For all the purchases in the last 30 days, show me the user's name and tier"
SELECT
  u.name,
  u.tier,
  e.created_at,
  (e.payload->>'amount')::numeric AS amount,
  e.payload->>'country' AS country
FROM events e
JOIN users u ON u.id = (e.payload->>'user_id')::bigint
WHERE e.payload @> '{"action": "purchase"}'
  AND e.created_at > now() - interval '30 days'
ORDER BY e.created_at DESC
LIMIT 100;

What needs indexes:

1. WHERE e.payload @> '{"action": "purchase"}' → GIN over payload.
2. WHERE e.created_at > ... → B-tree over created_at.
3. JOIN ... = (e.payload->>'user_id')::bigint → an expression index over ((payload->>'user_id')::bigint).
4. ORDER BY e.created_at DESC LIMIT 100 → if the plan picks a scan by created_at, the already-ordered index helps.

Validation:

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, u.tier, e.created_at, (e.payload->>'amount')::numeric, e.payload->>'country'
FROM events e
JOIN users u ON u.id = (e.payload->>'user_id')::bigint
WHERE e.payload @> '{"action": "purchase"}'
  AND e.created_at > now() - interval '30 days'
ORDER BY e.created_at DESC
LIMIT 100;

A typical plan when everything is well indexed:

Limit
  ->  Sort
        Sort Key: e.created_at DESC
        ->  Hash Join
              Hash Cond: ((e.payload->>'user_id')::bigint = u.id)
              ->  Bitmap Heap Scan on events e
                    Recheck Cond: ...
                    ->  BitmapAnd
                          ->  Bitmap Index Scan on events_payload_idx
                          ->  Bitmap Index Scan on events_created_at_idx
              ->  Hash
                    ->  Seq Scan on users u

Without the expression index over (payload->>'user_id')::bigint, the planner would do a Hash Join loading the 500k events into memory — it works but it's slower. With the index, it can go users → a direct lookup into events.

An optimization: invert the order if users is the more selective table

If your query really filters by a property of users (say tier = 'enterprise', which is only 100 users):

SELECT u.name, e.created_at, (e.payload->>'amount')::numeric
FROM users u
JOIN events e ON (e.payload->>'user_id')::bigint = u.id
WHERE u.tier = 'enterprise'
  AND e.payload @> '{"action": "purchase"}'
  AND e.created_at > now() - interval '30 days';

The planner can start from the 100 enterprise users and for each one do an index lookup into events (thanks to the expression index). Much more efficient than filtering 500k events first.

EXPLAIN ANALYZE tells you which strategy it chose. Don't predict — validate.

Aggregations: jsonb_agg, jsonb_object_agg, jsonb_build_*

PostgreSQL has a rich set of functions for building JSONB in aggregations. Let's go to the ones that show up in real code.

jsonb_agg: aggregate values into an array

"For each user, give me all the amounts of their purchases in an array."

SELECT
  (e.payload->>'user_id')::bigint AS user_id,
  jsonb_agg((e.payload->>'amount')::numeric) AS amounts
FROM events e
WHERE e.payload @> '{"action": "purchase"}'
GROUP BY (e.payload->>'user_id')::bigint
LIMIT 5;

-- Output:
--  user_id |       amounts
-- ---------+----------------------
--    12345 | [50.30, 120.00, 9.50]
--    67890 | [200.00]
--    11111 | [80.20, 45.10]

jsonb_agg is like array_agg but it produces a JSONB array.

jsonb_object_agg: build an aggregated object

"For each country, count how many purchases."

SELECT
  jsonb_object_agg(country, total) AS by_country
FROM (
  SELECT
    payload->>'country' AS country,
    COUNT(*) AS total
  FROM events
  WHERE payload @> '{"action": "purchase"}'
  GROUP BY payload->>'country'
) sub;

-- Output:
--                by_country
-- ---------------------------------------
--  {"AR": 12345, "ES": 9876, "MX": 11023, "US": 14552}

Useful when you want to return the result to the client in a nested object instead of rows.

jsonb_build_object and jsonb_build_array: build literal JSONB

-- Build an object from columns
SELECT jsonb_build_object(
  'user_id', user_id,
  'name', name,
  'tier', tier
) AS user_json
FROM users
LIMIT 3;

-- Output:
--                       user_json
-- -----------------------------------------------------
--  {"user_id": 1, "name": "user_1", "tier": "free"}
--  {"user_id": 2, "name": "user_2", "tier": "pro"}
--  ...

It's the equivalent of "build the endpoint's JSON response in pure SQL." Useful when the ORM serializes slowly and you want to avoid the round-trip.

Combination: a complete hierarchical aggregation

"For each country, give me the total number of purchases, the ranking of the top 3 users by amount, and the last purchase."

WITH purchases AS (
  SELECT
    e.payload->>'country' AS country,
    (e.payload->>'user_id')::bigint AS user_id,
    (e.payload->>'amount')::numeric AS amount,
    e.created_at
  FROM events e
  WHERE e.payload @> '{"action": "purchase"}'
),
top_users AS (
  SELECT
    country,
    user_id,
    SUM(amount) AS total_amount,
    ROW_NUMBER() OVER (PARTITION BY country ORDER BY SUM(amount) DESC) AS rank
  FROM purchases
  GROUP BY country, user_id
)
SELECT
  p.country,
  COUNT(*) AS total_purchases,
  ROUND(SUM(p.amount), 2) AS total_amount,
  jsonb_object_agg(t.user_id::text, t.total_amount) FILTER (WHERE t.rank <= 3) AS top_users,
  jsonb_build_object(
    'last_purchase_at', MAX(p.created_at)
  ) AS extra
FROM purchases p
LEFT JOIN top_users t ON t.country = p.country
GROUP BY p.country
ORDER BY total_amount DESC;

A query like that returns, in a single pass, the complete JSON that the dashboard endpoint would show:

 country | total_purchases | total_amount |              top_users                |        extra
---------+-----------------+--------------+----------------------------------------+----------------------
 US      |          14552  |   1234567.50 | {"42": 5500.10, "7": 4300.00, ...}    | {"last_purchase_at":..}
 MX      |          11023  |    889012.30 | {"123": 4100.00, "88": 3200.50, ...}  | {"last_purchase_at":..}

Why it matters: what with an ORM and N queries would be serious N+1 problems, in pure SQL is a single query that the planner optimizes. The jsonb_* functions let you assemble the exact output the frontend expects, without re-processing in Python.

ORDER BY over JSONB fields

GIN doesn't speed up ORDER BY. If your query orders by a field extracted from the JSONB and the table is big, you're going to pay for an in-memory sort.

-- ORDER BY over an extracted field — no dedicated index, in-memory sort
SELECT id, payload->>'amount' AS amount
FROM events
WHERE payload @> '{"action": "purchase"}'
ORDER BY (payload->>'amount')::numeric DESC
LIMIT 100;

If the GIN filters down to 5,000 rows and then sorts + limits to 100, that's OK. If the GIN filters down to 500,000 rows and then sorts + limits, the sort is expensive.

Solution: an expression index over the ORDER BY expression.

CREATE INDEX events_amount_desc_idx ON events
  (((payload->>'amount')::numeric) DESC)
  WHERE payload @> '{"action": "purchase"}';

What happens:

• An index sorted descending over amount, partial (purchases only).
• The planner can do an Index Scan + Limit without sorting anything in memory.

EXPLAIN ANALYZE
SELECT id, payload->>'amount'
FROM events
WHERE payload @> '{"action": "purchase"}'
ORDER BY (payload->>'amount')::numeric DESC
LIMIT 100;

-- Limit
--   ->  Index Scan using events_amount_desc_idx on events
-- Execution Time: very fast

Trade-off: this partial index exists specifically for that query. If you have 5 queries with different ORDER BYs, you're going to have 5 partials. That's the reality of serious optimization — every hot query has its dedicated index.

Pagination with JSONB

Classic OFFSET/LIMIT pagination degrades with a large OFFSET (covered in guide #12). In JSONB queries this is worse because each row is more expensive to "read" (extracting JSONB).

Recommended pattern: keyset pagination.

-- Page 1
SELECT id, payload->>'amount' AS amount, created_at
FROM events
WHERE payload @> '{"action": "purchase"}'
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Next page: use the last seen row as the cursor
SELECT id, payload->>'amount' AS amount, created_at
FROM events
WHERE payload @> '{"action": "purchase"}'
  AND (created_at, id) < ('2026-04-15 10:30:00', 12345)  -- cursor of the last one seen
ORDER BY created_at DESC, id DESC
LIMIT 50;

With an expression index over (created_at DESC, id DESC) (it can be partial over purchases), each page is O(log n) per seek, no matter whether you're on page 1 or page 1000.

Worked example: a complete endpoint

Build the "Top countries dashboard" endpoint: for the last 30 days, return per country the number of purchases, the total amount, the top 3 users by amount, and the last purchase. All in a single query.

The final query

WITH recent_purchases AS (
  SELECT
    e.payload->>'country' AS country,
    (e.payload->>'user_id')::bigint AS user_id,
    (e.payload->>'amount')::numeric AS amount,
    e.created_at
  FROM events e
  WHERE e.payload @> '{"action": "purchase"}'
    AND e.created_at >= now() - interval '30 days'
),
top_users AS (
  SELECT
    country,
    user_id,
    SUM(amount) AS user_total,
    ROW_NUMBER() OVER (PARTITION BY country ORDER BY SUM(amount) DESC) AS rank
  FROM recent_purchases
  GROUP BY country, user_id
),
top_users_per_country AS (
  SELECT
    country,
    jsonb_object_agg(user_id::text, user_total) AS top_users
  FROM top_users
  WHERE rank <= 3
  GROUP BY country
)
SELECT
  rp.country,
  COUNT(*) AS total_purchases,
  ROUND(SUM(rp.amount), 2) AS total_amount,
  COALESCE(t.top_users, '{}'::jsonb) AS top_users,
  jsonb_build_object('last_purchase_at', MAX(rp.created_at)) AS extra
FROM recent_purchases rp
LEFT JOIN top_users_per_country t ON t.country = rp.country
GROUP BY rp.country, t.top_users
ORDER BY total_amount DESC;

The indexes it needs

-- For the JSONB filter
CREATE INDEX events_payload_idx ON events USING gin(payload jsonb_path_ops);

-- For the date filter
CREATE INDEX events_created_at_idx ON events (created_at);

-- If this query is very frequent (a hot dashboard), a partial:
CREATE INDEX events_purchase_recent_idx ON events USING gin(payload jsonb_path_ops)
WHERE payload @> '{"action": "purchase"}';

Validate the plan

EXPLAIN (ANALYZE, BUFFERS) <the query>;

The ideal plan: BitmapAnd with the two indexes filters fast, the CTEs materialize once, the final joins are cheap.

Why does this matter in real work?

1. Real-time dashboard endpoints are built like this.

The product team asks for "a per-country view with top users and metrics." With queries like the worked example, the endpoint responds in 50-200ms. Without them (doing the aggregations in Python), it responds in seconds and needs a cache.

2. Replacing N queries with 1.

Every CTE/JOIN/aggregation is a "mental query." With naive SQLAlchemy, that's N queries (N+1). With pure SQL and jsonb_*, it's 1. Knowing how to write queries like this saves you "endpoint X is slow" tickets before they appear.

3. The frontend asks for nested JSON, and you can return it pre-assembled.

jsonb_object_agg and jsonb_build_object let the shape of the response come built from the DB. You eliminate the "transform rows into a hierarchical structure" step in Python — faster, less code, fewer bugs.

4. JOINs with JSONB fields are the key step.

Almost every app has tables with JSONB that (logically) point at other tables. Knowing how to create the right expression index + write the JOIN so the planner uses it is the skill that separates "usable JSONB" from "JSONB nightmare."

Traps and common mistakes

Mistake 1 (conceptual): assuming one GIN covers everything

Symptom: "I have GIN, why is the JOIN slow?"

Why it happens: GIN doesn't speed up JOINs by field equality. A JOIN needs a B-tree (expression index) over the extracted field.

How to fix it: create an expression index for each field used in a JOIN, ORDER BY, or filters that @> doesn't express.

Mistake 2 (practical): an inconsistent cast between the query and the index

Symptom: the index ((payload->>'user_id')::bigint) isn't used because the query does (payload->>'user_id')::int.

Why it happens: PostgreSQL treats bigint and int as different types. The index is over one, the query over the other.

How to fix it: absolute consistency. Decide on the canonical cast (typically bigint for IDs) and use it in the index and the queries.

Mistake 3 (performance): ORDER BY over 100k rows with no index

Symptom: a query with ORDER BY (payload->>'created_at')::timestamptz DESC LIMIT 50 takes seconds even though the WHERE filter is fast.

Why it happens: without a sorted index, the sort is done in memory over all the rows that pass the filter.

How to detect it: EXPLAIN shows a Sort with disk or memory: large in the plan. That's the signature of the expensive sort.

How to fix it: a sorted expression index: CREATE INDEX ON events (((payload->>'created_at')::timestamptz) DESC). Or better, move it to a real column outside the JSONB if you use it constantly.

Mistake 4 (conceptual): jsonb_agg without GROUP BY → a giant array

Symptom: SELECT jsonb_agg(payload) FROM events (without GROUP BY) tries to aggregate every row into a single array. Memory explodes on large tables.

Why it happens: aggregation without a partition tries to produce a single value.

How to fix it: always GROUP BY something (country, user_id, whatever makes sense). If you need "everything in one array" for an API response, paginate first, then aggregate.

Mistake 5 (conceptual): confusingly mixing WHERE with HAVING

Symptom: JSONB filters in HAVING when they should be in WHERE. Horrible performance because HAVING is evaluated post-aggregation.

Why it happens: "HAVING is for filtering" gets confused. HAVING filters groups (the results of the aggregation). WHERE filters rows (before aggregating).

How to fix it: filters on individual columns → WHERE. Filters on aggregations (COUNT(*) > 10) → HAVING.

-- ❌ Wrong: a row filter in HAVING
SELECT country, COUNT(*) FROM events
GROUP BY country
HAVING country = 'US';   -- runs the aggregation for ALL countries, then filters

-- ✅ Right: a row filter in WHERE
SELECT country, COUNT(*) FROM events
WHERE payload->>'country' = 'US'
GROUP BY country;

Mistake 6 (practical): N+1 disguised as a "single query"

Symptom: a "fast" main query but the endpoint makes 50 additional queries per row to "enrich" it.

Why it happens: the main query returns user_ids, then for each row the Python code makes an additional SELECT to the DB.

How to detect it: query logging (module 4 of guide #12). If you see 51 queries for "one page," it's N+1.

How to fix it: JOIN in SQL instead of a loop in Python. Use the techniques from this capsule. (Covered in depth in guide #12.)

Exercises

Exercise 1: BitmapAnd with two indexes

Over the events table from the setup, write a query that filters by (a) payload @> '{"action": "purchase"}' and (b) created_at > now() - interval '7 days'. Validate with EXPLAIN that the planner uses BitmapAnd.

-- Assuming the indexes exist:
-- CREATE INDEX events_payload_idx ON events USING gin(payload jsonb_path_ops);
-- CREATE INDEX events_created_at_idx ON events (created_at);

EXPLAIN ANALYZE
SELECT count(*) FROM events
WHERE payload @> '{"action": "purchase"}'
  AND created_at > now() - interval '7 days';

-- Expected plan:
-- Aggregate
--   ->  Bitmap Heap Scan on events
--         Recheck Cond: ...
--         ->  BitmapAnd
--               ->  Bitmap Index Scan on events_payload_idx
--                     Index Cond: (payload @> ...)
--               ->  Bitmap Index Scan on events_created_at_idx
--                     Index Cond: (created_at > ...)

Exercise 2: JOIN with an expression index

Write a query that returns the names and emails of the users who made a purchase with an amount > 200 in the last 30 days. Make sure the plan uses the expression index over ((payload->>'user_id')::bigint).

EXPLAIN (ANALYZE, BUFFERS)
SELECT DISTINCT u.name, u.email
FROM events e
JOIN users u ON u.id = (e.payload->>'user_id')::bigint
WHERE e.payload @> '{"action": "purchase"}'
  AND (e.payload->>'amount')::numeric > 200
  AND e.created_at > now() - interval '30 days';

-- Expected plan:
-- Hash Join (or Nested Loop if few events are filtered)
--   ->  Bitmap Heap Scan on events e
--         BitmapAnd with events_payload_idx + events_created_at_idx
--   ->  Hash on users (or Index Scan if it goes by user_id)

\timing on
-- With the index:
SELECT count(*) FROM ...;  -- e.g.: 100 ms

DROP INDEX events_user_id_idx;
-- Without the index:
SELECT count(*) FROM ...;  -- e.g.: 350 ms

CREATE INDEX events_user_id_idx ON events (((payload->>'user_id')::bigint));

Exercise 3: hierarchical aggregation

Write a query that returns, for each country, a JSONB object with: total purchases, total amount, average amount, minimum amount, maximum amount. Use jsonb_build_object to assemble the response.

SELECT
  payload->>'country' AS country,
  jsonb_build_object(
    'total_purchases', COUNT(*),
    'total_amount', ROUND(SUM((payload->>'amount')::numeric), 2),
    'avg_amount', ROUND(AVG((payload->>'amount')::numeric), 2),
    'min_amount', MIN((payload->>'amount')::numeric),
    'max_amount', MAX((payload->>'amount')::numeric)
  ) AS stats
FROM events
WHERE payload @> '{"action": "purchase"}'
  AND created_at > now() - interval '30 days'
GROUP BY payload->>'country'
ORDER BY SUM((payload->>'amount')::numeric) DESC;

 country |                                  stats
---------+------------------------------------------------------------------------
 US      | {"total_purchases": 1455, "total_amount": 73210.50, "avg_amount": 50.31, ...}
 MX      | {"total_purchases": 1102, "total_amount": 56089.20, ...}
 ...

Exercise 4: indexed ORDER BY

Write a query that returns the last 50 purchases sorted by (payload->>'amount')::numeric descending. Create the right expression index and validate with EXPLAIN that an Index Scan is used (not an in-memory Sort).

-- Descending sorted index, partial over purchases
CREATE INDEX events_amount_desc_idx ON events
  (((payload->>'amount')::numeric) DESC)
  WHERE payload @> '{"action": "purchase"}';

-- Refresh the stats
ANALYZE events;

-- Query
EXPLAIN ANALYZE
SELECT id, (payload->>'amount')::numeric AS amount, payload->>'country'
FROM events
WHERE payload @> '{"action": "purchase"}'
ORDER BY (payload->>'amount')::numeric DESC
LIMIT 50;

Limit
  ->  Index Scan using events_amount_desc_idx on events
        Filter: ...

Limit
  ->  Sort
        Sort Method: top-N heapsort
        ->  Bitmap Heap Scan on events
              ...

Exercise 5: keyset pagination

Implement keyset pagination over purchases sorted by created_at DESC, id DESC. Show the first page and the cursor for the next one.

-- Page 1
SELECT id, created_at, (payload->>'amount')::numeric AS amount, payload->>'country'
FROM events
WHERE payload @> '{"action": "purchase"}'
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Output:
--      id     |       created_at        | amount | country
-- ------------+-------------------------+--------+---------
--   500000   | 2026-05-01 23:55:12+00  | 89.50  | US
--   499998   | 2026-05-01 23:54:58+00  | 320.00 | MX
--   ...      | ...                     | ...    | ...
--   499951   | 2026-05-01 23:30:11+00  | 150.00 | ES
-- (50 rows)

-- Cursor: the last one of page 1
-- last_created_at = '2026-05-01 23:30:11+00'
-- last_id = 499951

-- Page 2
SELECT id, created_at, (payload->>'amount')::numeric AS amount, payload->>'country'
FROM events
WHERE payload @> '{"action": "purchase"}'
  AND (created_at, id) < ('2026-05-01 23:30:11+00', 499951)
ORDER BY created_at DESC, id DESC
LIMIT 50;

CREATE INDEX events_purchase_keyset_idx ON events
  (created_at DESC, id DESC)
  WHERE payload @> '{"action": "purchase"}';

Exercise 6: detect and fix a slow query

They hand you this query in a code review:

SELECT
  e.id,
  e.payload->>'amount',
  u.name,
  u.email
FROM events e
LEFT JOIN users u ON u.id = (e.payload->>'user_id')::int
WHERE e.payload->>'country' = 'US'
  AND e.payload->>'action' = 'purchase'
  AND e.created_at > now() - interval '7 days'
ORDER BY (e.payload->>'amount')::numeric DESC
LIMIT 100;

EXPLAIN shows a Seq Scan + an in-memory Sort + a slow Hash Join. What changes do you propose?

-- 1. Rewrite the filters with @>
-- 2. Consistent bigint cast
-- 3. Create a partial sorted expression index

CREATE INDEX events_amount_desc_purchase_idx ON events
  (((payload->>'amount')::numeric) DESC)
  WHERE payload @> '{"action": "purchase"}';

-- Refactored query:
SELECT
  e.id,
  (e.payload->>'amount')::numeric AS amount,
  u.name,
  u.email
FROM events e
LEFT JOIN users u ON u.id = (e.payload->>'user_id')::bigint
WHERE e.payload @> '{"action": "purchase", "country": "US"}'
  AND e.created_at > now() - interval '7 days'
ORDER BY (e.payload->>'amount')::numeric DESC
LIMIT 100;

Summary and next step

In this capsule you learned:

• Think of queries in layers: filtering (GIN), JOIN/enrichment (expression index), aggregation/order (expression index over the specific expression).
• Combining @> with classic SQL (date ranges, numeric comparisons) uses BitmapAnd. It works if both sides are selective.
• JOINs with JSONB fields require an expression index with a consistent cast. Without it, a Hash Join over the whole table.
• JSONB aggregation functions (jsonb_agg, jsonb_object_agg, jsonb_build_object) let you build the endpoint's response in pure SQL, eliminating transformation work in Python.
• ORDER BY over JSONB fields needs a sorted expression index. GIN doesn't sort. Otherwise, an in-memory sort that scales badly.
• Keyset pagination over JSONB is viable and fast with an expression index over (order, id) DESC.
• EXPLAIN ANALYZE on every complex query. Every plan change (BitmapAnd → BitmapOr, Index Scan → Sort, Hash Join → Nested Loop) has a measurable impact.

Before moving on you should be able to:

• Design a query with JSONB filters + JOIN + aggregation + ordering, knowing which index feeds each part
• Write "dashboard" endpoints that return structured JSON in a single query
• Identify in EXPLAIN when the plan is bad (Seq Scan, in-memory Sort, Hash Join without an index lookup) and fix it
• Apply keyset pagination over large JSONB tables

Next capsule — JSONB Anti-Patterns. You now have the full arsenal of "how to use JSONB well." Capsule 07 is the filter: when NOT to use JSONB, which patterns kill performance, how to spot "JSONB for relational data" in a code review, and cases where the team is "already committed" to an anti-pattern and has to migrate. It's the capsule that separates you from the dev who abuses JSONB because "it's flexible" and makes you understand that flexibility has a cost.

Resources

1. PostgreSQL 16 Documentation — JSON Functions and Operators (aggregates) — jsonb_agg, jsonb_object_agg, and all the aggregation functions.
2. PostgreSQL 16 Documentation — Bitmap Index Scan — how PostgreSQL combines multiple indexes in BitmapAnd/BitmapOr.
3. pganalyze — "Postgres Query Patterns: JSONB" — applied patterns with EXPLAIN.
4. Crunchy Data — "Working with JSON in PostgreSQL" — a complete tutorial with aggregation cases.
5. Markus Winand — "Use the Index, Luke! — Pagination" — the classic reference on keyset pagination (it applies to JSONB too).
6. Hussein Nasser — "Postgres JSON Aggregation" — a video walkthrough with practical cases.
7. PostgreSQL Wiki — Performance Tips for Joins — when each JOIN type wins, tuning parameters.

Module 1 — Advanced PostgreSQL for Backend Guide

Next capsule: JSONB Anti-Patterns — when NOT to use JSONB and how to spot it in a code review.