Skip to main content
frogbot.aggregate() totals the documents of one collection per group: deal value per stage, tickets per status and assignee, or story points per sprint. Grouping and totals run in the database, so no document is loaded into memory. It runs the collection’s read access the same way find does, so a total never includes a document the caller can’t read. The Postgres, Vercel Postgres, SQLite, Cloudflare D1, and MongoDB adapters implement aggregate, and every option on this page behaves the same way on each. Any other adapter fails the call with AggregateCapabilityError (501).

Query

Name each total in values. The names come back in each group’s values and can be used in sort:
Each value is one of: A value’s field must hold one value per document: a field on the document itself or inside a group, not across a relationship, inside an array, or a hasMany number. That way every document adds to a total once. A value name can’t equal a groupBy path or start with -. The result types keep your names: values.value is a number, values.average is number | null.

Aggregate options

Any other option fails with AggregateValidationError.

Group by

groupBy takes three forms:
You can group by text, select, radio, email, number, checkbox, date, and single-collection relationship and upload fields. Polymorphic relationships, JSON, rich text, and blocks fail with AggregateValidationError. Each group’s group object is keyed by the groupBy path, or the date field’s name for a date bucket.

Date buckets

by is day, week, month, quarter, or year. Weeks are ISO weeks, starting on Monday. Every bucket is in UTC, and its key is the bucket’s start as an ISO timestamp, such as 2026-01-05T00:00:00.000Z for the week of 5 January 2026. A deal closed at 6 pm on Monday in California falls on Tuesday in UTC. Shift where ranges, or group by a stored local date field, when you need local days.

Missing values

Documents without a value for a key form one group whose key is null. The null group always sorts last, whichever direction you sort the key.

Many values per document

A document with several values for a key joins one group per distinct value. Such a key is a hasMany select or relationship, or a field inside an array.
  • A deal tagged hot and big counts once under hot and once under big, so group counts can add up to more than count.
  • A value that the document repeats, such as two array rows with the same sku, still counts once.

Relationship keys

Relationship keys come back as IDs. With depth of 1 or more, each key is replaced with the related document, shaped by populate. A related document the caller can’t read stays an ID.

Totals without groups

Leave out groupBy to total every matching document in one row, such as the footer of a list:
The result always has that one row.
  • No match. When nothing matches, counts and sums are 0 and the rest are null. The same applies when the caller can’t read the collection and disableErrors is true.
  • No sorting or paging. Without groupBy, sort, limit, and page fail with AggregateValidationError, because there is nothing to sort or page.

Results and paging

The result has the same paging keys as findDistinct, with the groups in groups:
Results are numbers on every database. Text keys sort alphabetically everywhere, select fields included.

Access

Aggregate runs as req.user unless overrideAccess: true:
  • Collection read. false fails with 403, or returns no groups with disableErrors: true. A Where narrows the documents that count.
  • Related read. A groupBy path through a relationship applies the related collection’s read rule:
    • A Where narrows the documents to those whose related document it allows, plus those with no related document, which stay in the null group.
    • false fails with QueryError (400), as a where on that path would, or returns no groups with disableErrors: true.
    • A Where on a path through a hasMany relationship or an array, such as partners.tier, fails the same way. A document can link both a readable and an unreadable account, and its groups would show the unreadable account’s tier.
  • Field read. A field with its own access.read rule behaves differently in each option:
    • In where, you can filter on it. The where runs the rule with the request, as find does, and fails with QueryError (400) for a user that the rule denies.
    • In values, it is null in every group, while the other values still total.
    • In groupBy, it fails with 403, for every user, because find runs the rule once per document and a total can’t.
    • overrideAccess: true totals and groups it. Run admin reports from your own endpoint with overrideAccess: true after checking the user.
  • Hidden fields. A field with hidden: true fails with 403 in groupBy and values, even with overrideAccess: true, as findDistinct does. In where, it fails only without overrideAccess.
Collection beforeOperation and afterOperation hooks don’t run for aggregate.

Drafts and locales

  • Drafts. On a collection with drafts, only published documents count. draft: true adds drafts that were never published. A published document with a newer draft still counts its published values, unlike find with draft: true (#361).
  • Published check. On a localized collection, the published check uses the requested locale, so a document published only in en is left out of an fr aggregate.
  • Localized fields. Localized fields in groupBy and values read the requested locale.

REST API

Every collection has a POST /api/{collection-slug}/aggregate endpoint. The JSON body accepts the same options as frogbot.aggregate() except collection, overrideAccess, req, and context: the endpoint always runs as the requesting user with access enforced.
From a frontend, the SDK has a typed aggregate(options, init?).

Errors

Both aggregate errors are exported from frogbot. The REST endpoint returns the same statuses.

Adapter integration

A database adapter adds aggregate through an aggregate object, beside search.
  1. FrogBot checks options, runs access, and resolves fields.
  2. The adapter receives a Where with access already applied, the group and value fields with their stored types, and the sort.
  3. The adapter returns one row per group with raw keys and totals, plus the group count.
  4. FrogBot turns the keys and totals into numbers and ISO dates.
The SQL adapters use sqliteAggregateAdapter and postgresAggregateAdapter from frogbot/aggregate.