Queries complejos: filtros, JOINs y agregaciones sobre JSONB

Descripción de la cápsula

Las cápsulas anteriores te dieron las piezas: operadores, path queries, GIN, partials, expression indexes. Esta cápsula te muestra cómo se ensamblan en queries reales — el tipo que ves en endpoints de producción, no en docs. Vas a combinar WHERE con @>, JOIN con campos extraídos del JSONB, GROUP BY con agregaciones JSONB-aware (jsonb_agg, jsonb_object_agg), y ORDER BY con expression indexes que el GIN no acelera.

El tema central es: cuando JSONB se mete en queries que también usan SQL relacional clásico, no todo se acelera con un solo GIN. Vas a aprender a identificar qué parte de la query necesita qué index — el GIN para la parte de búsqueda, expression indexes para JOIN/ORDER BY, partial indexes para hot paths recurrentes — y vas a aprender las funciones de agregación JSONB que son las que el código real necesita pero rara vez aparecen en tutoriales.

Al terminar vas a poder escribir el endpoint completo del proyecto del módulo: filtros JSONB, JOIN con tabla relacional, agregaciones jerárquicas, paginación, todo con plan de query validado. Es la cápsula que conecta "técnica suelta" con "código de aplicación real".

Modelo mental: piensa la query por capas

Una query JSONB seria suele tener tres capas. Cada capa elige su índice apropiado.

┌───────────────────────────────────────────────────────────────────┐
│                                                                   │
│  Capa 1: FILTRADO                                                 │
│    WHERE payload @> '...'                                         │
│    → GIN index sobre payload                                      │
│    → reduce el dataset rápido                                     │
│                                                                   │
│  Capa 2: JOIN / ENRICHMENT                                        │
│    JOIN users ON users.id = (payload->>'user_id')::bigint         │
│    → expression index sobre ((payload->>'user_id')::bigint)       │
│    → resuelve relaciones                                          │
│                                                                   │
│  Capa 3: AGGREGACIÓN / ORDEN / PROYECCIÓN                         │
│    GROUP BY payload->>'country', SUM((payload->>'amount')::num)   │
│    ORDER BY total DESC LIMIT 10                                   │
│    → puede necesitar expression indexes adicionales               │
│      o materialización temprana                                   │
│                                                                   │
└───────────────────────────────────────────────────────────────────┘

Patrón mental: Cuando escribes una query JSONB compleja, identificá las tres capas. Para cada una, preguntate: "¿qué índice ayuda?". Si una capa no tiene índice, vas a pagar un Seq Scan o un sort que arruina la performance, aunque las otras estén perfectas.

Filtrado: combinar @> con condiciones SQL

La forma más común de combinar JSONB con SQL es: @> para la parte JSONB + condición SQL adicional.

-- Tabla de eventos del módulo
SELECT id, payload->>'amount' AS amount
FROM events
WHERE payload @> '{"action": "purchase"}'
  AND created_at >= '2026-04-01'
  AND created_at < '2026-05-01';

Lo que pasa:

• WHERE payload @> ... usa GIN. Filtra rápido.
• WHERE created_at BETWEEN ... usa B-tree sobre created_at (asumiendo que existe).
• El planner combina los dos filtros con bitmap AND: cada index produce un bitmap, los intersecta, y solo lee las filas en común.

Validación:

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 es la firma de "el planner combinó dos indexes". Es típicamente eficiente cuando ambos filtros son selectivos.

Filtros que NO se expresan con @>

@> es para coincidencias exactas de valor. No funciona para:

• Comparaciones numéricas (>, <, BETWEEN).
• Pattern matching (LIKE, regex).
• IS NULL o IS NOT NULL sobre campos extraídos.

Para esos, combinás:

1. Lo que se puede con @> (filtros exactos por valor).
2. Lo que no, con condición adicional (sin index dedicado, o con expression index).

-- Filtros exactos (GIN) + comparación numérica (post-filter en memoria o expression index)
SELECT id, payload->>'amount' AS amount
FROM events
WHERE payload @> '{"action": "purchase", "country": "US"}'
  AND (payload->>'amount')::numeric > 100;

Si la primera condición es selectiva (digamos, devuelve 1000 filas), el > 100 adicional sobre 1000 filas se hace en memoria sin problema. Si la primera condición devuelve 100k filas y la segunda corta a 10k, vale la pena un expression index sobre ((payload->>'amount')::numeric).

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

-- Ahora la query puede usar bitmap AND con ambos:
EXPLAIN ANALYZE SELECT id FROM events
WHERE payload @> '{"action": "purchase"}'
  AND (payload->>'amount')::numeric > 100;
-- BitmapAnd con events_payload_idx Y events_amount_idx

JOIN: extraer campos del JSONB para joinear con tablas relacionales

Caso muy común: tabla con JSONB que tiene user_id adentro, quieres joinear con la tabla users para enriquecer.

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;

Query con JOIN

-- "Para todos los purchases de los últimos 30 días, mostrame el nombre y tier del user"
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;

Lo que necesita índices:

1. WHERE e.payload @> '{"action": "purchase"}' → GIN sobre payload.
2. WHERE e.created_at > ... → B-tree sobre created_at.
3. JOIN ... = (e.payload->>'user_id')::bigint → expression index sobre ((payload->>'user_id')::bigint).
4. ORDER BY e.created_at DESC LIMIT 100 → si el plan elige scan por created_at, el index ya ordenado ayuda.

Validación:

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;

Plan típico cuando todo está bien indexado:

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

Sin el expression index sobre (payload->>'user_id')::bigint, el planner haría Hash Join cargando los 500k events en memoria — funciona pero más lento. Con el index, puede ir users → lookup directo a events.

Una optimización: invertir el orden si users es la tabla más selectiva

Si tu query realmente filtra por una propiedad de users (digamos tier = 'enterprise' que son solo 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';

El planner puede empezar por los 100 users enterprise y para cada uno hacer un index lookup en events (gracias al expression index). Mucho más eficiente que filtrar 500k events primero.

EXPLAIN ANALYZE te dice qué estrategia eligió. No predijas — validá.

Agregaciones: jsonb_agg, jsonb_object_agg, jsonb_build_*

PostgreSQL tiene un set rico de funciones para construir JSONB en agregaciones. Vamos a las que aparecen en código real.

jsonb_agg: agregar valores en un array

"Para cada user, dame todos los amounts de sus purchases en un 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 es como array_agg pero produce un JSONB array.

jsonb_object_agg: construir objeto agregado

"Para cada country, contar cuántos 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}

Útil cuando quieres devolver el resultado al cliente en un objeto anidado en lugar de filas.

jsonb_build_object y jsonb_build_array: construir JSONB literal

-- Construir un objeto desde columnas
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"}
--  ...

Es el equivalente de "construir el JSON de respuesta del endpoint en SQL puro". Útil cuando el ORM serializa lento y quieres evitar el round-trip.

Combinación: agregación jerárquica completa

"Para cada country, dame el total de purchases, el ranking de los top 3 users por amount, y el último 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;

Una query así devuelve, en una sola pasada, el JSON completo que el endpoint del dashboard mostraría:

 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":..}

Por qué importa: lo que con un ORM y N queries serían N+1 problems graves, en SQL puro es una query única que el planner optimiza. Las funciones jsonb_* permiten armar el output exacto que el frontend espera, sin re-procesar en Python.

ORDER BY sobre campos JSONB

GIN no acelera ORDER BY. Si tu query ordena por un campo extraído del JSONB y la tabla es grande, vas a pagar un sort en memoria.

-- ORDER BY sobre campo extraído — sin index dedicado, sort en memoria
SELECT id, payload->>'amount' AS amount
FROM events
WHERE payload @> '{"action": "purchase"}'
ORDER BY (payload->>'amount')::numeric DESC
LIMIT 100;

Si el GIN filtra a 5,000 filas y luego sort + limit a 100, está OK. Si el GIN filtra a 500,000 filas y sort + limit, el sort es caro.

Solución: expression index sobre la expresión de ORDER BY.

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

Lo que pasa:

• Index ordenado descendente sobre amount, parcial (solo purchases).
• El planner puede hacer Index Scan + Limit sin ordenar nada en memoria.

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: muy rápido

Trade-off: este partial index existe específicamente para esa query. Si tienes 5 queries con ORDER BY distinto, vas a tener 5 partials. Es la realidad de optimización seria — cada query caliente tiene su index dedicado.

Paginación con JSONB

La paginación clásica con OFFSET/LIMIT degrada con OFFSET grande (cubierto en guía #12). En queries JSONB esto es peor porque cada fila se "lee" más cara (extraer JSONB).

Pattern recomendado: keyset pagination.

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

-- Página siguiente: usar el last seen como 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 del último visto
ORDER BY created_at DESC, id DESC
LIMIT 50;

Con expression index sobre (created_at DESC, id DESC) (puede ser parcial sobre purchases), cada página es O(log n) por seek, sin importar si estás en página 1 o página 1000.

Ejemplo trabajado: endpoint completo

Construí el endpoint "Top countries dashboard": para los últimos 30 días, devolver por country el número de purchases, el monto total, los 3 top users por monto, y el último purchase. Todo en una sola query.

Query final

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;

Indexes que necesita

-- Para el filtro JSONB
CREATE INDEX events_payload_idx ON events USING gin(payload jsonb_path_ops);

-- Para el filtro de fecha
CREATE INDEX events_created_at_idx ON events (created_at);

-- Si esta query es muy frecuente (dashboard caliente), partial:
CREATE INDEX events_purchase_recent_idx ON events USING gin(payload jsonb_path_ops)
WHERE payload @> '{"action": "purchase"}';

Validar el plan

EXPLAIN (ANALYZE, BUFFERS) <la query>;

Plan ideal: BitmapAnd con los dos indexes filtra rápido, las CTEs se materializan una vez, los joins finales son baratos.

¿Por qué importa esto en el trabajo real?

1. Endpoints de dashboard real-time se construyen así.

El equipo de producto pide "vista por país con top users y métricas". Con queries como el ejemplo trabajado, el endpoint responde en 50-200ms. Sin ellas (haciendo agregaciones en Python), responde en segundos y necesita caché.

2. Reemplazo de N queries por 1.

Cada CTE/JOIN/agregación es una "query mental". Con SQLAlchemy ingenuo, eso son N queries (N+1). Con SQL puro y jsonb_*, es 1. Saber escribir queries así te ahorra tickets de "el endpoint X es lento" antes de que aparezcan.

3. Frontend pide JSON anidado, tú puedes devolverlo armado.

jsonb_object_agg y jsonb_build_object permiten que el shape del response venga construido del DB. Eliminás el paso "transformar filas a estructura jerárquica" en Python — más rápido, menos código, menos bugs.

4. JOINs con campos JSONB son el paso clave.

Casi todas las apps tienen tablas con JSONB que apuntan (lógicamente) a otras tablas. Saber crear el expression index correcto + escribir el JOIN para que el planner lo use es el skill que diferencia "JSONB usable" de "JSONB pesadilla".

Trampas y errores comunes

Error 1 (conceptual): asumir que un GIN cubre todo

Síntoma: "tengo GIN, ¿por qué el JOIN es lento?"

Por qué pasa: GIN no acelera JOINs por igualdad de campo. JOIN necesita B-tree (expression index) sobre el campo extraído.

Cómo corregir: crear expression index para cada campo que se usa en JOIN, ORDER BY o filtros que @> no expresa.

Error 2 (práctico): cast inconsistente entre query e index

Síntoma: index ((payload->>'user_id')::bigint) no se usa porque la query hace (payload->>'user_id')::int.

Por qué pasa: PostgreSQL trata bigint y int como tipos diferentes. El index es sobre uno, la query sobre otro.

Cómo corregir: consistencia absoluta. Decidí el cast canónico (típicamente bigint para IDs) y úsalo en index y queries.

Error 3 (performance): ORDER BY sobre 100k filas sin index

Síntoma: query con ORDER BY (payload->>'created_at')::timestamptz DESC LIMIT 50 tarda segundos aunque el filtro WHERE es rápido.

Por qué pasa: sin index ordenado, el sort se hace en memoria sobre todas las filas que pasan el filtro.

Cómo detectar: EXPLAIN muestra Sort con disk o memory: large en el plan. Es la firma del sort costoso.

Cómo corregir: expression index ordenado: CREATE INDEX ON events (((payload->>'created_at')::timestamptz) DESC). O mejor, llevarlo a una columna real fuera del JSONB si lo usas constantemente.

Error 4 (conceptual): jsonb_agg sin GROUP BY → array gigante

Síntoma: SELECT jsonb_agg(payload) FROM events (sin GROUP BY) intenta agregar todas las filas en un solo array. Memoria explota en tablas grandes.

Por qué pasa: agregación sin partición intenta producir un solo valor.

Cómo corregir: siempre GROUP BY algo (country, user_id, lo que tenga sentido). Si necesitás "todo en un array" para una respuesta API, paginá primero, después agrega.

Error 5 (conceptual): mezclar WHERE con HAVING confusamente

Síntoma: filtros JSONB en HAVING cuando deberían estar en WHERE. Performance horrible porque HAVING se evalúa post-agregación.

Por qué pasa: "HAVING para filtrar" se confunde. HAVING filtra grupos (resultados de la agregación). WHERE filtra filas (antes de agregar).

Cómo corregir: filtros sobre columnas individuales → WHERE. Filtros sobre agregaciones (COUNT(*) > 10) → HAVING.

-- ❌ Mal: filtro de fila en HAVING
SELECT country, COUNT(*) FROM events
GROUP BY country
HAVING country = 'US';   -- corre la agregación de TODOS los países, después filtra

-- ✅ Bien: filtro de fila en WHERE
SELECT country, COUNT(*) FROM events
WHERE payload->>'country' = 'US'
GROUP BY country;

Error 6 (práctico): N+1 disfrazado de "query única"

Síntoma: una query principal "rápida" pero el endpoint hace 50 queries adicionales por cada fila para "enriquecer".

Por qué pasa: la query principal devuelve user_ids, después por cada fila el código Python hace un SELECT adicional al DB.

Cómo detectar: logging de queries (módulo 4 de guía #12). Si ves 51 queries para "una página", es N+1.

Cómo corregir: JOIN en SQL en lugar de loop en Python. Usá las técnicas de esta cápsula. (Cubierto en profundidad en guía #12.)

Ejercicios

Ejercicio 1: BitmapAnd con dos indexes

Sobre la tabla events del setup, escribe una query que filtre por (a) payload @> '{"action": "purchase"}' y (b) created_at > now() - interval '7 days'. Validá con EXPLAIN que el planner usa BitmapAnd.

-- Asumiendo que existen los indexes:
-- 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';

-- Plan esperado:
-- 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 > ...)

Ejercicio 2: JOIN con expression index

Escribí una query que devuelva los nombres y emails de los usuarios que hicieron purchase con amount > 200 en los últimos 30 días. Asegurate de que el plan use el expression index sobre ((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';

-- Plan esperado:
-- Hash Join (o Nested Loop si hay pocos events filtrados)
--   ->  Bitmap Heap Scan on events e
--         BitmapAnd con events_payload_idx + events_created_at_idx
--   ->  Hash on users (o Index Scan si va por user_id)

\timing on
-- Con index:
SELECT count(*) FROM ...;  -- ej: 100 ms

DROP INDEX events_user_id_idx;
-- Sin index:
SELECT count(*) FROM ...;  -- ej: 350 ms

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

Ejercicio 3: agregación jerárquica

Escribí una query que devuelva, para cada country, un objeto JSONB con: total de purchases, monto total, monto promedio, monto mínimo, monto máximo. Usá jsonb_build_object para armar la respuesta.

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 (jsonb_build_object('total', SUM((payload->>'amount')::numeric)))->>'total' DESC;

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

Ejercicio 4: ORDER BY indexado

Escribí una query que devuelva los últimos 50 purchases ordenados por (payload->>'amount')::numeric descendente. Creá el expression index correcto y validá con EXPLAIN que se usa Index Scan (no Sort en memoria).

-- Index ordenado descendente, partial sobre purchases
CREATE INDEX events_amount_desc_idx ON events
  (((payload->>'amount')::numeric) DESC)
  WHERE payload @> '{"action": "purchase"}';

-- Refrescar 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
              ...

Ejercicio 5: keyset pagination

Implementá keyset pagination sobre purchases ordenados por created_at DESC, id DESC. Mostrá la primera página y el cursor para la siguiente.

-- Página 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 filas)

-- Cursor: el último de la página 1
-- last_created_at = '2026-05-01 23:30:11+00'
-- last_id = 499951

-- Página 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"}';

Ejercicio 6: detectar y arreglar query lenta

Te pasan esta query del 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 muestra Seq Scan + Sort en memoria + Hash Join lento. ¿Qué cambios proponés?

-- 1. Reescribir filtros con @>
-- 2. Cast consistente bigint
-- 3. Crear expression index ordenado parcial

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

-- Query refactorizada:
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;

Resumen y siguiente paso

En esta cápsula aprendiste:

• Pensá las queries por capas: filtrado (GIN), JOIN/enrichment (expression index), agregación/orden (expression index sobre la expresión específica).
• Combinar @> con SQL clásico (rango de fechas, comparaciones numéricas) usa BitmapAnd. Funciona si ambos lados son selectivos.
• JOINs con campos JSONB requieren expression index con cast consistente. Sin esto, Hash Join sobre toda la tabla.
• Funciones de agregación JSONB (jsonb_agg, jsonb_object_agg, jsonb_build_object) permiten construir el response del endpoint en SQL puro, eliminando trabajo de transformación en Python.
• ORDER BY sobre campos JSONB necesita expression index ordenado. GIN no ordena. Si no, sort en memoria que escala mal.
• Keyset pagination sobre JSONB es viable y rápida con expression index sobre (orden, id) DESC.
• EXPLAIN ANALYZE en cada query compleja. Cada cambio de plan (BitmapAnd → BitmapOr, Index Scan → Sort, Hash Join → Nested Loop) tiene impacto medible.

Antes de avanzar deberías poder:

• Diseñar una query con filtros JSONB + JOIN + agregación + orden, sabiendo qué index alimenta cada parte
• Escribir endpoints "dashboard" que devuelvan JSON estructurado en una sola query
• Identificar en EXPLAIN cuándo el plan está mal (Seq Scan, Sort en memoria, Hash Join sin index lookup) y corregirlo
• Aplicar keyset pagination sobre tablas JSONB grandes

Siguiente cápsula — JSONB Anti-Patterns. Ya tienes todo el arsenal de "cómo usar JSONB bien". La cápsula 07 es el filtro: cuándo NO usar JSONB, qué patterns matan performance, cómo identificar el "JSONB para datos relacionales" en code review, y casos donde el equipo "ya está comprometido" con un anti-pattern y hay que migrar. Es la cápsula que te diferencia del dev que abusa de JSONB porque "es flexible" y entiende que la flexibilidad tiene costo.

Recursos

1. PostgreSQL 16 Documentation — JSON Functions and Operators (aggregates) — jsonb_agg, jsonb_object_agg y todas las funciones de agregación.
2. PostgreSQL 16 Documentation — Bitmap Index Scan — cómo PostgreSQL combina múltiples indexes en BitmapAnd/BitmapOr.
3. pganalyze — "Postgres Query Patterns: JSONB" — patterns aplicados con EXPLAIN.
4. Crunchy Data — "Working with JSON in PostgreSQL" — tutorial completo con casos de aggregation.
5. Markus Winand — "Use the Index, Luke! — Pagination" — referencia clásica sobre keyset pagination (aplica a JSONB también).
6. Hussein Nasser — "Postgres JSON Aggregation" — walkthrough en video con casos prácticos.
7. PostgreSQL Wiki — Performance Tips for Joins — cuándo cada tipo de JOIN gana, parámetros de tuning.

Módulo 1 — Advanced PostgreSQL for Backend Guide

Siguiente cápsula: JSONB Anti-Patterns — cuándo NO usar JSONB y cómo detectarlo en code review.