This page ⧉Copy as Markdown M↓View Markdown ✦Open in ChatGPT ✧Open in Claude Hide panelsShow panels ⌘Ctrl \

Aggregate

Runs a MongoDB aggregation pipeline on a collection. The body is the list of stages. The answer is what the pipeline makes.

In short

• POST /api/schema/<user-path>/<instance>/<database>/<collection>/aggregate
• MongoDB only. An SQL table answers 400 : This API is only supported for mongodb.
• The column permissions of the groups do not apply to its answer.

Method	POST
URL	/api/schema/admin/mongodb/inventory/customers/aggregate
Body	the pipeline : a list of stages
Query params	none
Answer	data : the documents the pipeline makes
Databases	MongoDB only
Cached	yes, when enableCaching is on for the collection or the database
From code	g.sys.db.aggregate
API id	SCHEMA_POST_AGGREGATE
Without a schema	/api/gen/admin/mongodb/inventory/customers/aggregate : below

The call

POST /api/schema/admin/mongodb/inventory/orders/aggregate

Body : the paid orders of each customer, the biggest total first

[
    { "$match": { "status": "PAID" } },
    { "$group": { "_id": "$customer_id", "orders": { "$sum": 1 }, "total": { "$sum": "$total" } } },
    { "$sort": { "total": -1 } },
    { "$limit": 10 }
]

Answer

{ "success": true, "statusCode": 200, "data": [ { "_id": 1, "orders": 4, "total": 51600 } ] }

A body that is not a list is refused : Please provide array for aggregate body.

Stages

Every stage of your version of MongoDB works. The ones used most :

Stage	Example
$match	{ "$match": { "pincode": { "$gt": 380000 } } }
$group	{ "$group": { "_id": "$pincode", "count": { "$sum": 1 } } }
$project	{ "$project": { "name": { "$toLower": "$first_name" } } }
$addFields	{ "$addFields": { "full_name": { "$concat": [ "$first_name", " ", "$last_name" ] } } }
$sort, $skip, $limit	{ "$sort": { "total": -1 } }
$count	{ "$count": "customers" }
$bucket	{ "$bucket": { "groupBy": "$price", "boundaries": [ 0, 100, 500 ], "default": "Other", "output": { "count": { "$sum": 1 } } } }
$facet	several pipelines in one : { "$facet": { "byPrice": [ … ], "byCategory": [ … ] } }
$lookup	a join inside MongoDB : { "$lookup": { "from": "orders", "localField": "customer_id", "foreignField": "customer_id", "as": "orders" } }
$unwind	{ "$unwind": "$orders" }
$collStats	{ "$collStats": { "storageStats": {}, "count": {} } }

What API Maker adds

• Values in $match are taken for what they look like : a text that looks like an ObjectId becomes one, an ISO date becomes a date. They do not take the types of the schema.
• Related tables. On the schema API, the first $match can filter on a column of a related table, as a find and join : { "$match": { "customer_id.loyalty_tier": "GOLD" } }.
• $sort as a text works too : { "$sort": "-total,_id" }.
• Big pipelines may use the disk of MongoDB (allowDiskUse).

Good to know

• Permissions. The groups decide who may call this API. But the column permissions of a group do not apply to what a pipeline answers : a $project or a $lookup can show any field. Give this API only to groups that may read every field.
• SQL. For a report on an SQL database, run SQL with executeQuery from a custom API.
• Cache. With caching on, a pipeline answers from Redis until the collection changes.

From your code

g.sys.db.aggregate makes the same call from custom APIs, hooks, events, schedulers and test cases.

Without a schema : /api/gen

Every collection also answers this operation under /api/gen, with or without a schema :

URL	/api/gen/admin/mongodb/inventory/customers/aggregate
API id	GEN_POST_AGGREGATE
From code	g.sys.db.gen.aggregateGen

The same pipeline, without find and join : a key with dots goes to MongoDB as it is.

Headers and errors

This operation takes the request headers of every API. It answers the envelope, with the status codes and the messages of every API.

Related : All APIs · Table schema · Pre hooks · Post hooks · API group permission