Aggregate - Permission Mongo

Overview

The aggregate endpoint allows you to execute complex MongoDB aggregation pipelines on your collections. The pipeline is automatically enhanced with RBAC filters to ensure users only access data they have permission to read.

Request

collection

string

required

The name of the collection to aggregate

pipeline

array

required

Array of aggregation pipeline stages. Each stage is an object with MongoDB aggregation operators.Maximum stages: 20 (configurable)

Basic Aggregation Example

{
  "pipeline": [
    {
      "$match": {
        "status": "active"
      }
    },
    {
      "$group": {
        "_id": "$department",
        "count": { "$sum": 1 },
        "avg_salary": { "$avg": "$salary" }
      }
    },
    {
      "$sort": { "count": -1 }
    }
  ]
}

Advanced Pipeline Example

{
  "pipeline": [
    {
      "$match": {
        "created_at": {
          "$gte": "2024-01-01T00:00:00Z"
        }
      }
    },
    {
      "$lookup": {
        "from": "departments",
        "localField": "department_id",
        "foreignField": "_id",
        "as": "department"
      }
    },
    {
      "$unwind": "$department"
    },
    {
      "$group": {
        "_id": {
          "department": "$department.name",
          "month": { "$month": "$created_at" }
        },
        "total_sales": { "$sum": "$amount" },
        "order_count": { "$sum": 1 }
      }
    },
    {
      "$project": {
        "_id": 0,
        "department": "$_id.department",
        "month": "$_id.month",
        "total_sales": 1,
        "order_count": 1,
        "avg_order_value": {
          "$divide": ["$total_sales", "$order_count"]
        }
      }
    }
  ]
}

Response

data

array

Array of documents resulting from the aggregation pipeline. The structure depends on your pipeline stages, particularly $group and $project operations.

Response Example

{
  "data": [
    {
      "_id": "Engineering",
      "count": 45,
      "avg_salary": 95000
    },
    {
      "_id": "Product",
      "count": 23,
      "avg_salary": 88000
    },
    {
      "_id": "Sales",
      "count": 18,
      "avg_salary": 75000
    }
  ]
}

Aggregation Pipeline Stages

$match - Filter Documents

{
  "$match": {
    "status": "active",
    "age": { "$gte": 18 }
  }
}

Filters documents to pass only those that match the specified condition(s).

$group - Group and Accumulate

{
  "$group": {
    "_id": "$category",
    "total": { "$sum": "$amount" },
    "count": { "$sum": 1 },
    "avg": { "$avg": "$amount" },
    "max": { "$max": "$amount" },
    "min": { "$min": "$amount" }
  }
}

Groups documents by a specified expression and applies accumulator expressions. Accumulator operators:

$sum: Calculate sum
$avg: Calculate average
$min, $max: Find minimum/maximum
$first, $last: Get first/last value
$push: Build array of values
$addToSet: Build array of unique values

$project - Reshape Documents

{
  "$project": {
    "_id": 0,
    "name": 1,
    "full_name": {
      "$concat": ["$first_name", " ", "$last_name"]
    },
    "age_group": {
      "$cond": [
        { "$gte": ["$age", 18] },
        "adult",
        "minor"
      ]
    }
  }
}

Reshapes documents by including, excluding, or adding new fields.

$sort - Sort Documents

{
  "$sort": {
    "created_at": -1,
    "name": 1
  }
}

Sorts documents by specified fields (1 = ascending, -1 = descending).

$limit - Limit Results

{
  "$limit": 100
}

Limits the number of documents passed to the next stage.

$skip - Skip Documents

{
  "$skip": 20
}

Skips a specified number of documents.

$lookup - Join Collections

{
  "$lookup": {
    "from": "orders",
    "localField": "_id",
    "foreignField": "user_id",
    "as": "user_orders"
  }
}

Performs a left outer join to another collection.

$unwind - Deconstruct Arrays

{
  "$unwind": "$items"
}

Deconstructs an array field to output a document for each element.

$addFields - Add Computed Fields

{
  "$addFields": {
    "total_price": {
      "$multiply": ["$quantity", "$unit_price"]
    }
  }
}

Adds new fields to documents.

RBAC Enforcement

From handlers_query.go:353-388: The aggregation pipeline is automatically enhanced with RBAC filters:

RBAC filter injection: A $match stage with RBAC filters is prepended to the pipeline
Filter merging: If your pipeline starts with a $match, the RBAC filter is merged with it
Automatic enforcement: Users only see aggregated data from documents they have permission to read

// Example: RBAC filter is automatically added
// User's pipeline:
[
  { "$group": { "_id": "$department", "count": { "$sum": 1 } } }
]

// Actual executed pipeline:
[
  { "$match": { "tenant_id": "user-tenant-123" } },  // RBAC filter
  { "$group": { "_id": "$department", "count": { "$sum": 1 } } }
]

Pipeline Limits

From handlers_query.go:345-351:

pipeline

array

Maximum 20 stages by default (configurable via scalability.max_aggregation_stages)

From handlers_query.go:404-410:

data

array

Maximum 10,000 results by default (configurable via scalability.max_result_size)

Common Use Cases

Sales Analytics

{
  "pipeline": [
    {
      "$match": {
        "order_date": {
          "$gte": "2024-01-01T00:00:00Z",
          "$lt": "2024-12-31T23:59:59Z"
        }
      }
    },
    {
      "$group": {
        "_id": {
          "year": { "$year": "$order_date" },
          "month": { "$month": "$order_date" }
        },
        "revenue": { "$sum": "$total_amount" },
        "orders": { "$sum": 1 }
      }
    },
    {
      "$sort": { "_id.year": 1, "_id.month": 1 }
    }
  ]
}

User Engagement Report

{
  "pipeline": [
    {
      "$match": {
        "last_login": { "$exists": true }
      }
    },
    {
      "$group": {
        "_id": {
          "$cond": [
            { "$gte": ["$last_login", { "$subtract": [new Date(), 7*24*60*60*1000] }] },
            "active",
            "inactive"
          ]
        },
        "count": { "$sum": 1 }
      }
    }
  ]
}

Top Performers

{
  "pipeline": [
    {
      "$match": { "status": "active" }
    },
    {
      "$group": {
        "_id": "$sales_rep_id",
        "total_sales": { "$sum": "$amount" },
        "deals_closed": { "$sum": 1 }
      }
    },
    {
      "$sort": { "total_sales": -1 }
    },
    {
      "$limit": 10
    },
    {
      "$lookup": {
        "from": "users",
        "localField": "_id",
        "foreignField": "_id",
        "as": "rep"
      }
    },
    {
      "$unwind": "$rep"
    },
    {
      "$project": {
        "name": "$rep.name",
        "total_sales": 1,
        "deals_closed": 1
      }
    }
  ]
}

Error Responses

error

object

code

string

Error code:

collection_not_found: Collection does not exist
forbidden: User lacks aggregate permission
bad_request: Invalid pipeline (too many stages, invalid syntax)
internal_error: Aggregation execution failed

message

string

Human-readable error message

details

object

Additional error context

Error Examples

{
  "error": {
    "code": "bad_request",
    "message": "Aggregation pipeline exceeds maximum allowed stages",
    "details": {
      "max_stages": 20,
      "actual_stages": 25
    }
  }
}

{
  "error": {
    "code": "bad_request",
    "message": "Aggregation result exceeds maximum allowed size",
    "details": {
      "max_size": 10000,
      "actual_size": 15000
    }
  }
}

Performance Considerations

Aggregation pipelines can be resource-intensive on large collections. Follow these best practices:

Start with $match: Filter data early in the pipeline to reduce documents processed
Use indexes: Ensure fields used in $match and $sort are indexed
Limit early: Use $limit as soon as possible if you don’t need all results
Avoid $lookup on large collections: Joins can be expensive; consider denormalization
Monitor result size: Keep aggregation results under the max result size limit

Code Reference

Implementation: pkg/api/handlers_query.go:299-420

List Documents - Simple queries with filtering and pagination
Count Documents - Count matching documents
Get Document - Retrieve a single document

​Overview

​Request

​Basic Aggregation Example

​Advanced Pipeline Example

​Response

​Response Example

​Aggregation Pipeline Stages

​$match - Filter Documents

​$group - Group and Accumulate

​$project - Reshape Documents

​$sort - Sort Documents

​$limit - Limit Results

​$skip - Skip Documents

​$lookup - Join Collections

​$unwind - Deconstruct Arrays

​$addFields - Add Computed Fields

​RBAC Enforcement

​Pipeline Limits

​Common Use Cases

​Sales Analytics

​User Engagement Report

​Top Performers

​Error Responses

​Error Examples

​Performance Considerations

​Code Reference

​Related Endpoints

Overview

Request

Basic Aggregation Example

Advanced Pipeline Example

Response

Response Example

Aggregation Pipeline Stages

$match - Filter Documents

$group - Group and Accumulate

$project - Reshape Documents

$sort - Sort Documents

$limit - Limit Results

$skip - Skip Documents

$lookup - Join Collections

$unwind - Deconstruct Arrays

$addFields - Add Computed Fields

RBAC Enforcement

Pipeline Limits

Common Use Cases

Sales Analytics

User Engagement Report

Top Performers

Error Responses

Error Examples

Performance Considerations

Code Reference

Related Endpoints