Aggregate
POST /{coll}/_aggregate (route) groups documents and computes counts, sums, averages and more, inside the daemon. The rows come back like the rows of a search (a JSON array, or NDJSON with Accept), with the same headers and pagination.
curl --unix-socket /run/ndb/ndb.sock http://ndb/calls/_aggregate -d '{
"pipeline": [
{ "$match": { "duration": { "$gte": 1 } } },
{ "$group": { "_id": "$status", "n": { "$count": {} }, "avg": { "$avg": "$duration" } } },
{ "$sort": { "n": -1 } }
],
"limit": 100
}'
# [{"_id":"answered","n":2,"avg":35.5},{"_id":"failed","n":1,"avg":3}]res, err := calls.Aggregate(ctx, ndb.Pipeline{
{"$match": map[string]any{"duration": map[string]any{"$gte": 1}}},
{"$group": map[string]any{
"_id": "$status",
"n": map[string]any{"$count": map[string]any{}},
"avg": map[string]any{"$avg": "$duration"},
}},
ndb.SortStage(ndb.Desc("n")),
}, ndb.AggLimit(100))
// res.Rows, res.Next, res.Total (with ndb.AggCount())page = calls.aggregate([
{ "$match" => { "duration" => { "$gte" => 1 } } },
{ "$group" => {
"_id" => "$status",
"n" => { "$count" => {} },
"avg" => { "$avg" => "$duration" }
} },
{ "$sort" => { "n" => -1 } }
],
limit: 100)
page.rows # => [{"_id" => "answered", "n" => 2, "avg" => 35.5}, ...]Call.where(duration: 1..).group(:status).count # => {"answered" => 2, "failed" => 1}
Call.where(duration: 1..).group(:status).average(:duration)
# a full pipeline goes through the client
Ndb::Rails.client.aggregate(Call.collection_name, pipeline: [...])const res = await calls.aggregate<{ _id: string; n: number; avg: number }>(
[
{ $match: { duration: { $gte: 1 } } },
{ $group: { _id: '$status', n: { $count: {} }, avg: { $avg: '$duration' } } },
{ $sort: { n: -1 } },
],
{ limit: 100 },
)
// res.rows, res.next, res.total (with count: true)Body
{
"pipeline": [
{ "$match": { "ts": { "$gte": "2026-11-01T00:00:00Z" } } },
{ "$group": {
"_id": { "status": "$status", "hour": { "$dateTrunc": { "date": "$ts", "unit": "hour" } } },
"n": { "$count": {} },
"avg_duration": { "$avg": "$duration" }
} },
{ "$sort": { "_id.hour": 1 } },
{ "$limit": 1000 }
],
"limit": 100, "skip": 0, "after": "<cursor>", "count": true
}limit, skip, after and count page the result rows, like in a search.
Pipeline shape
- Exactly one
$group. - Before it:
$match(all are ANDed), one$sort, and one$unwind. The$sortmatters only for$firstand$last. A$matchafter the$unwindfilters its rows. - After it:
$match,$sort,$limit,$skip,$project. $matchtakes the filters offind. A$matchbefore$groupuses indexes and zone maps likefinddoes.
Stages
| Stage | Where | Meaning |
|---|---|---|
$match | before and after $group | keep the documents (or groups) that match a filter |
$having | after $group | the same as $match there, named as in SQL: keep the groups that match ({"calls": {"$gt": 10}}). Before $group it is invalid_pipeline: use $match. |
$unwind | before $group (once) | one row per element of an array, see $unwind |
$sort | before $group (once), after $group | order. Before the group it only decides $first and $last. |
$group | once | build one row per key, see below |
$limit, $skip | after $group | cut the group rows |
$project | after $group | keep or remove fields of the group rows |
$lookup | at the end | add documents of another collection to each output row, see Lookup |
$unwind
$unwind makes one row per element of an array, so $group can group by the elements. In each row, the path of the array holds that element.
{"pipeline": [
{"$match": {"status": "paid"}},
{"$unwind": "$items"},
{"$group": {"_id": "$items.sku", "qty": {"$sum": "$items.qty"}, "orders": {"$distinct": "$_id"}}},
{"$sort": {"qty": -1}},
{"$limit": 10}
]}With the orders {"_id": "o1", "items": [{"sku": "a", "qty": 2}, {"sku": "b", "qty": 1}]} and {"_id": "o2", "items": [{"sku": "a", "qty": 5}]}, the rows are {"_id": "a", "qty": 7, "orders": 2} and {"_id": "b", "qty": 1, "orders": 1}.
The rules are those of MongoDB:
| The value at the path | Rows |
|---|---|
| an array with elements | one per element |
an empty array, null, or no field | none (one, with preserveNullAndEmptyArrays) |
| not an array | one, with the value |
The long form has two options:
{"$unwind": {"path": "$items", "preserveNullAndEmptyArrays": true, "includeArrayIndex": "i"}}preserveNullAndEmptyArrayskeeps a document with no element as one row. In the row of an empty array the field is left out, so a group by it puts that row with the missing andnullones.includeArrayIndexnames a top-level field (not_id) that gets the place of the element, from 0 (nullwhen the value is not an array).
Notes:
- A
$matchbefore$unwinduses indexes and zone maps. A$matchafter it is tested on each row ({"items.qty": {"$gte": 2}}keeps the elements with that quantity), with no index. So put the filters of the document first. $countcounts rows (elements). To count documents, use{"$distinct": "$_id"}.$firstand$lastfollow the order of the documents, then of the elements.- The path goes through objects only (
$order.items), not through another array. - One
$unwindper pipeline, before$group. A pipeline with$unwindneeds a$group. Else400 invalid_pipeline. - The array is read from the document, not from a column. An
$unwindon many documents is slower than a group on a plain field: filter first.
Group key
$group._id is:
- a field path:
"$status". Paths start with$:"$a.b". A path through an array gives an array value. - a constant, or
nullfor one group with every document. - a
$dateTrunc, to bucket time. - an object of those, for a composite key:
{"status": "$status", "hour": {"$dateTrunc": ...}}.
Missing and null are the same key. Groups come out sorted by _id.
A path of a blind field ("$cpf") groups by the hash of its value, and each group gets the value back as its _id (plain with ?decrypt=1 and the right). Any other field of encrypted can only be in $first and $last.
Accumulators
| Accumulator | Result |
|---|---|
$count | number of documents. Its argument is {}. |
$sum | sum of the numbers. Other values are ignored. No number gives 0. |
$avg | sum / count of the numbers. No number gives null. |
$min, $max | lowest / highest value in the cross-type order. Nothing gives null. |
$first, $last | value of the first / last document of the group, in the order of the $sort before the group. |
$distinct | number of distinct values (COUNT(DISTINCT), the cardinality of Elasticsearch), exact. Missing and null are not counted. |
$addToSet | the distinct values, as an array sorted in the cross-type order. Missing and null are left out. |
The distinct values of $distinct and $addToSet count against group_limit: a group over it is group_limit_exceeded, not a wrong count.
How many times each caller called, and the callers that called more than 10 times:
{"pipeline": [
{"$match": {"type": "finishcall"}},
{"$group": {"_id": "$ani", "calls": {"$count": {}}}},
{"$having": {"calls": {"$gt": 10}}},
{"$sort": {"calls": -1}}
]}How many distinct callers each customer had, and which hangup causes:
{"pipeline": [
{"$match": {"type": "finishcall"}},
{"$group": {"_id": "$customer", "callers": {"$distinct": "$ani"}, "causes": {"$addToSet": "$hangup_cause"}}},
{"$sort": {"callers": -1}},
{"$limit": 10}
]}Time buckets
{ "$dateTrunc": { "date": "$ts", "unit": "hour", "timezone": "Europe/Rome" } }- Units:
minute,hour,day,week(Monday),month. - The default timezone is UTC.
- The result is the bucket start as an RFC 3339 UTC string.
- A value that is not a time gives
null.
Project
{"$project": {"n": 1}} keeps fields; {"$project": {"n": 0}} removes them. Do not mix. _id stays unless listed with 0.
Lookup
$lookup stages go at the end of the pipeline: they add, to each output row, documents of another collection. They take the MongoDB names, or one relation of the lookup of a search:
{
"pipeline": [
{ "$group": { "_id": "$customer_id", "total": { "$sum": "$total" } } },
{ "$sort": { "total": -1 } },
{ "$limit": 10 },
{ "$lookup": { "from": "customers", "localField": "_id", "foreignField": "_id", "as": "customer" } }
]
}{ "_id": "c1", "total": 125, "customer": [ { "_id": "c1", "name": "Ana" } ] }- MongoDB names:
from,localField,foreignField,as. As in MongoDB,asis always an array.pipelineandletare not supported. - One relation of a search:
{ "$lookup": { "customer": { "belongs_to": "customers", "fk": "_id" } } }gives an object ornull. - A
$lookupbefore another stage is400 lookup_invalid. The rules of a search lookup apply: an indexed other side, 10 000 documents at most.
Limits
- The group table is limited in memory. Over the limit the answer is
group_limit_exceeded(HTTP 422): add a$matchor use fewer group keys. - An aggregate cursor runs the pipeline again for each page.
- A bad pipeline is
400 invalid_pipeline. See Errors for every code.