Skip to content
This page View Markdown Open in ChatGPT Open in Claude

Aggregate (schema API)

Runs a MongoDB aggregation pipeline on a collection : the body is the array of stages, the answer their result.
Method POST
URL /api/schema/admin/mongodb/inventory/customers/aggregate
Body the pipeline : an array of stages
Query params none
Answer data : the documents produced by the pipeline
Databases MongoDB only
Cached yes, with enableCaching on the table
From code g.sys.db.aggregate
API id SCHEMA_POST_AGGREGATE (groups, settings, hooks, WebSocket subscriptions)
The other family Aggregate as a generated API

Generated (/api/gen) or schema (/api/schema) ?

Both families offer the same operations on the same URL pattern. The schema APIs read the schema of the table : values are converted to their types, validated, encrypted, defaulted and numbered before they reach the database. The generated APIs are schemaless : they pass your data as it is, so a number sent as "22" stays a string. Use the schema APIs for the tables your application writes to, and the generated APIs for quick reads and for tables without a schema.

The URL

/api/schema/admin/mongodb/inventory/customers/aggregate : replace admin with the user path of your account, mongodb with your instance, inventory with the database and customers with the table. The examples use this table :

customer_id first_name last_name last_update pincode isActive
1 Bob lin 2022-11-14 04: 34: 58 382345 1
2 Alice Page 2022-10-15 02: 10: 40 382346 1
3 Mallory Brown 2022-09-13 03: 44: 05 382347 1
4 Eve Mathly 2022-11-12 01: 59: 33 382348 1
5 Eve Page 2022-11-12 01: 59: 33 382349 1

The call

POST /api/schema/admin/mongodb/inventory/customers/aggregate
Body : orders per customer, the biggest 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 which is not an array is refused with Please provide array for aggregate body.

Stages

Every stage of the MongoDB version you run is accepted. 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": {} } }

With a schema

  • The find of a first $match stage is converted with the schema (types, ObjectIds, dotted keys of relations), so { "$match": { "orders.status": "PAID" } } filters through the related collection as a find and join.

Good to know

  • Only MongoDB : SQL databases answer This API is only supported for mongodb. For SQL, run a query with executeQuery from a custom API.
  • Field permissions of the groups apply to what a stage returns. A caller who can not read a field does not get it, projected or not.
  • 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, with the same headers and params. Pass true as the second argument to get the whole response envelope instead of data.

Headers

Every call takes the request headers : the tokens, x-am-response-case, x-am-content-type-response (JSON, XML, YAML…), x-am-response-object-type: make_flat, x-am-cache-control, x-am-get-encrypted-data, x-am-internationalization, x-am-tenant-username, x-am-meta.

Errors

Code When
401 The token in x-am-authorization is missing or invalid.
403 No group of the API user grants this API of this table, or a field of the request.
404 The instance, database or table of the URL does not exist, or the table has no schema (use /api/gen).
400 The query or the body is wrong : the error messages say which key and why.