Aggregates

In this guide, we'll explore two ORM-native aggregation paths. Scalar metrics give you counts, sums, and averages through aggregateIndex. Ranked access gives you leaderboards, random picks, and ordered pagination through rankIndex + rank().

Scalar Metrics

The easiest way to aggregate. Declare an aggregateIndex on your table, and the ORM handles everything — no triggers, no manual maintenance.

Important: Scalar metrics are strict no-scan. Filtered aggregate/count paths require a declared aggregateIndex matching the filter shape. Missing index → COUNT_NOT_INDEXED or AGGREGATE_NOT_INDEXED error. Unfiltered count() uses the native Convex count syscall.

Declaring Aggregate Indexes

We'll start by telling the ORM what we want to aggregate. Add aggregateIndex to your table definition:

convex/functions/schema.ts

const users = convexTable('users', { orgId: text().notNull(), status: text(), email: text() }, (t) => [
  aggregateIndex('by_org_status').on(t.orgId, t.status),
]);

.on(fields) defines the filter key — you'll query filtered counts/aggregates by these fields. For unfiltered aggregate blocks (like _sum/_avg/_min/_max), use .all(). Plain count() without where uses the native syscall. You can chain metric declarations:

convex/functions/schema.ts

const orders = convexTable(
  'orders',
  {
    orgId: text().notNull(),
    status: text(),
    customerEmail: text(),
    amount: integer(),
    score: integer(),
  },
  (t) => [
    aggregateIndex('all_metrics')
      .all()
      .count(t.customerEmail)
      .sum(t.amount)
      .avg(t.amount)
      .min(t.score)
      .max(t.score),
    aggregateIndex('by_org_status')
      .on(t.orgId, t.status)
      .count(t.customerEmail)
      .sum(t.amount)
      .avg(t.amount)
      .min(t.score)
      .max(t.score),
  ]
);

After deploying, the CLI runs aggregateBackfill automatically. Wait for aggregateBackfillStatus to report READY before querying.

`count()`

Now you can count rows efficiently:

// Total users (native count syscall; no aggregateIndex required)
const totalUsers = await ctx.orm.query.users.count();

// Filtered count (requires aggregateIndex matching filter shape)
const activeUsers = await ctx.orm.query.users.count({
  where: { status: 'active' },
});

// Per-field non-null counts
const nonNulls = await ctx.orm.query.users.count({
  where: { status: 'active' },
  select: {
    _all: true,
    email: true,
  },
});

Filtered count() accepts index-compilable filters: eq, in, isNull, gt, gte, lt, lte, conjunction via AND, and bounded finite DNF OR when every branch is index-plannable on the same aggregateIndex. Field counts in select require .count(field) metric declarations on the matching aggregateIndex.

Missing or mismatched indexes throw deterministic errors — see Error Codes below.

For windowed counts with orderBy, skip, take, and cursor, see Windowed Count below.

`aggregate()`

For sum, avg, min, and max — use Prisma-style aggregate blocks:

const metrics = await ctx.orm.query.orders.aggregate({
  where: { orgId: 'org-1', status: 'paid' },
  _count: {
    _all: true,
    customerEmail: true,
  },
  _sum: {
    amount: true,
  },
  _avg: {
    amount: true,
  },
  _min: {
    score: true,
  },
  _max: {
    score: true,
  },
});

Same filter rules as count() — strict no-scan, index-backed. Supports bounded finite DNF OR when every branch is index-plannable and resolves to the same aggregateIndex. Unsupported args (callback where, relation filters) throw AGGREGATE_ARGS_UNSUPPORTED.

For windowed aggregate queries with orderBy, cursor, skip, and take, see Windowed Aggregate below.

`groupBy()`

For analytics-style queries, groupBy() lets you bucket rows and compute metrics per group. It follows the same strict no-scan guardrails:

by is required
every by field must have finite where constraints (eq/in/isNull)
orderBy supports by fields and selected metric fields
skip/take/cursor require explicit orderBy
having supports conjunction filters on by fields and selected metric outputs
OR/NOT in having are unsupported (AGGREGATE_FILTER_UNSUPPORTED)

const grouped = await ctx.orm.query.orders.groupBy({
  by: ['orgId'],
  where: {
    orgId: { in: ['org-1', 'org-2'] },
    status: 'paid',
  },
  _count: true,
  _sum: { amount: true },
  _avg: { amount: true },
  orderBy: [{ _count: 'desc' }, { _sum: { amount: 'desc' } }],
  having: { _count: { gt: 0 } },
  take: 10,
});

If any by field is unconstrained, groupBy() throws AGGREGATE_ARGS_UNSUPPORTED to avoid implicit bucket-wide scans.

Relation `_count`

You can load relation counts alongside query results — no N+1:

const projects = await ctx.orm.query.projects.findMany({
  with: { _count: { todos: true, members: true } },
});
// projects[0]._count?.todos => 5

You can filter the counted relations:

const users = await ctx.orm.query.users.findMany({
  with: {
    _count: {
      todos: { where: { completed: true } },
    },
  },
});
// users[0]._count?.todos => 3

Through-filtered _count also works for through() relations using indexed lookups:

const users = await ctx.orm.query.users.findMany({
  with: {
    _count: {
      memberTeams: { where: { name: 'Core' } },
    },
  },
});
// users[0]._count?.memberTeams => 1

Access counts via row._count?.relation ?? 0. Works on findMany, findFirst, and findFirstOrThrow.

Mutation `returning({ _count })`

You can also include _count in mutation returning selections — handy for returning fresh counts after a write:

const [user] = await ctx.orm
  .insert(usersTable)
  .values({ name: 'Alice' })
  .returning({
    id: usersTable.id,
    _count: { posts: true },
  });
// user._count?.posts => 0

This works with filters too:

const [updated] = await ctx.orm
  .update(usersTable)
  .set({ name: 'Bob' })
  .where(eq(usersTable.id, userId))
  .returning({
    id: usersTable.id,
    _count: { posts: { where: { status: 'published' } } },
  });
// updated._count?.posts => 2

Works on insert, update, and delete via split selection + relation count loading.

Ranked Access With `rankIndex`

For leaderboards, random picks, and ordered pagination, use rankIndex in schema and rank() in queries.

rank() is strict no-scan. You must query a declared rankIndex, and required partition fields must be constrained in where.

Operation	Description
`rank().indexOf({ id })`	Position/rank of a document
`rank().at(offset)`	Row at a specific position
`rank().paginate({ cursor, limit })`	Ordered page traversal
`rank().max()` / `rank().min()`	Extremes by rank order
`rank().random()`	Random row from ranked set
`rank().count()` / `rank().sum()`	Ranked-set count/sum

Declaring `rankIndex`

We'll start by declaring a leaderboard index on a table:

convex/functions/schema.ts

const scores = convexTable(
  'scores',
  {
    gameId: text().notNull(),
    score: integer().notNull(),
    createdAt: timestamp().notNull(),
    userId: text().notNull(),
  },
  (t) => [
    rankIndex('leaderboard')
      .partitionBy(t.gameId)
      .orderBy({ column: t.score, direction: 'desc' })
      .orderBy({ column: t.createdAt, direction: 'asc' })
      .sum(t.score),

    rankIndex('global_leaderboard')
      .all()
      .orderBy({ column: t.score, direction: 'desc' }),
  ]
);

partitionBy(...) replaces old "namespace" behavior. Each unique partition value gets its own ranked set.

Ranked Queries

Now let's query rankings. Call rank() with the index name and a where clause that covers the partition fields:

const leaderboard = ctx.orm.query.scores.rank('leaderboard', {
  where: { gameId },
});

const top10 = await leaderboard.paginate({ cursor: null, limit: 10 });
const userRank = await leaderboard.indexOf({ id: userId });
const thirdPlace = await leaderboard.at(2);
const best = await leaderboard.max();
const worst = await leaderboard.min();
const randomPick = await leaderboard.random();
const total = await leaderboard.count();
const totalScore = await leaderboard.sum();

Leaderboard + User Stats

This is a common pattern: scalar metrics from ORM + ranking from rank():

const gameScores = await ctx.orm.query.scores.findMany({
  where: { userId: ctx.userId, gameId: input.gameId },
  limit: 1000,
});

if (gameScores.length === 0) {
  return { highScore: 0, averageScore: 0, gamesPlayed: 0, globalRank: null, percentile: 0 };
}

const highScore = Math.max(...gameScores.map((s) => s.score));
const averageScore =
  gameScores.reduce((sum, s) => sum + s.score, 0) / gameScores.length;

const lb = ctx.orm.query.scores.rank('leaderboard', {
  where: { gameId: input.gameId },
});
const globalRank = await lb.indexOf({ id: ctx.userId });
const totalPlayers = await lb.count();

Repair

If rank or aggregate state gets out of sync after a bad deploy or interrupted write path, you can trigger a full rebuild from the CLI:

better-convex aggregate rebuild

Best Practices

Use `partitionBy` for Isolation

// ✅ Partition per tenant to isolate write hot spots
rankIndex('tenant_scores')
  .partitionBy(t.tenantId)
  .orderBy({ column: t.score, direction: 'desc' });

// ❌ Global rank can create cross-tenant contention
rankIndex('global_scores')
  .all()
  .orderBy({ column: t.score, direction: 'desc' });

Design `orderBy` for Query Patterns

// ✅ Order matches product reads: highest score first, oldest tie first
rankIndex('game_scores')
  .partitionBy(t.gameId)
  .orderBy({ column: t.score, direction: 'desc' })
  .orderBy({ column: t.createdAt, direction: 'asc' });

// ❌ Order does not match "top score" queries
rankIndex('game_scores_by_time')
  .partitionBy(t.gameId)
  .orderBy({ column: t.createdAt, direction: 'asc' });

When to Use

Need	Use
Counts, sums, averages	Scalar Metrics (`aggregateIndex` + ORM `count()`/`aggregate()`)
Relation counts	Relation `_count` (`with: { _count: { ... } }`)
Rankings, leaderboards	Ranked Access With `rankIndex` (`indexOf`, `at`, `paginate`)
Random document access	Ranked Access With `rankIndex` (`random`, `at`)
Sorted pagination	Ranked Access With `rankIndex` (`paginate({ cursor, limit })`)
Non-table data	Model it as a table, then use Scalar Metrics or Ranked Access With `rankIndex`

API Reference

Prisma Parity Matrix (No-Scan)

Prisma feature	Status	Notes
`aggregate({ _count/_sum/_avg/_min/_max, where })`	Supported	Bucket-backed, no base-table scan fallback
`aggregate({ _sum })` nullability	Supported	Returns `null` for empty/all-null sets
`groupBy({ by, where, _count/_sum/_avg/_min/_max })`	Supported	`by` fields must be finite-constrained (`eq/in/isNull`) in `where`
`groupBy({ having/orderBy/skip/take/cursor })`	Partial	Supported for finite index-bounded groups with conjunction-only `having`
`count()`	Supported	Native Convex count syscall
`count({ where })`	Supported	Indexed scalar subset
`count({ where, select: { _all, field } })`	Supported	Field counts require `aggregateIndex.count(field)`
`findMany({ with: { _count: { relation: true } } })`	Supported	Indexed relation counts
`findMany({ with: { _count: { relation: { where } } } })`	Supported	Direct relation scalar filters
`aggregate({ orderBy/take/skip/cursor })`	Partial	`orderBy/cursor` supported; `skip/take` is `_count`-only in v1
Advanced aggregate/count filters (`OR/NOT/string/relation`)	Partial	Bounded finite DNF `OR` rewrite is supported when branches resolve to one `aggregateIndex`; `NOT`/string/relation filters are blocked
Relation `_count` nested relation filter	Blocked	`RELATION_COUNT_FILTER_UNSUPPORTED`
`findMany({ distinct })`	Blocked	Not available under strict no-scan contract. Use `select().distinct({ fields })`
Relation `_count` filtered through relation	Supported	Indexed `through()` relation filters
Mutation return `_count` parity	Supported	`returning({ _count })` on insert/update/delete

Windowed Count

You can count rows within a window defined by ordering and bounds:

// Count with skip/take (pagination window)
const windowCount = await ctx.orm.query.todos.count({
  where: { projectId },
  orderBy: { createdAt: 'desc' },
  skip: 10,
  take: 50,
});

// Count with cursor (rows after a specific value)
const afterCursor = await ctx.orm.query.todos.count({
  where: { projectId },
  orderBy: { createdAt: 'desc' },
  cursor: { createdAt: lastSeen },
});

orderBy — sort direction for window bounds
skip — number of rows to skip before counting
take — max rows to count within the window
cursor — start counting after a specific field value (requires orderBy, single field in v1)

count({ select: { field: true } }) with skip/take/cursor is not supported in v1. Use count() or count({ select: { _all: true } }) for windowed counts.

Windowed Aggregate

aggregate() also supports window args with the same strict no-scan guardrails:

orderBy + cursor: supported for _count/_sum/_avg/_min/_max
skip/take: supported for _count only in v1

const metrics = await ctx.orm.query.orders.aggregate({
  where: { orgId: 'org-1' },
  orderBy: { amount: 'desc' },
  skip: 10,
  take: 100,
  _sum: { amount: true },
  _count: { _all: true },
});

skip/take with non-count metrics throws AGGREGATE_ARGS_UNSUPPORTED because metric window skip/take is not bucket-computable under strict no-scan in v1.

Error Codes

Error	Cause
`COUNT_NOT_INDEXED`	No `aggregateIndex` matches the filter shape
`COUNT_FILTER_UNSUPPORTED`	Filter uses unsupported operators (`NOT`, string, relation, or non-finite `OR`)
`COUNT_INDEX_BUILDING`	Index is still backfilling
`COUNT_RLS_UNSUPPORTED`	Called in an RLS-restricted context

`_sum` Nullability

_sum returns null for empty sets or when all field values are null (Prisma-compatible):

// Empty table or all-null amounts → { _sum: { amount: null } }
// Non-empty with values → { _sum: { amount: 1500 } }

Limitations

Consideration	Guideline
Update frequency	High-frequency updates to nearby keys cause contention
Key size	Keep composite keys reasonable (3-4 components max)
Partition cardinality	High-cardinality `partitionBy` values increase rank index overhead
Query patterns	Design keys for actual needs, not hypothetical

`findMany({ distinct })` (Unsupported)

findMany({ distinct }) is not available to keep strict index-backed no-scan guarantees.

If you need deduplication in query pipelines, use:

const page = await ctx.orm.query.todos
  .select()
  .distinct({ fields: ['status'] })
  .paginate({ cursor: null, limit: 100 });

That's it — you now have O(1) counts, Prisma-style aggregates, and O(log n) ranked access, all without scanning a single row.

Aggregates

Scalar Metrics

Declaring Aggregate Indexes

`count()`

`aggregate()`

`groupBy()`

Relation `_count`

Mutation `returning({ _count })`

Ranked Access With `rankIndex`

Declaring `rankIndex`

Ranked Queries

Leaderboard + User Stats

Repair

Best Practices

Use `partitionBy` for Isolation

Design `orderBy` for Query Patterns

When to Use

API Reference

Prisma Parity Matrix (No-Scan)

Windowed Count

Windowed Aggregate

Error Codes

`_sum` Nullability

Limitations

`findMany({ distinct })` (Unsupported)

Next Steps

Triggers

Rate Limiting

On this page