Using MongoDB Aggregates: The Power Behind acre Access Control

Overview

MongoDB aggregation pipelines are the foundation of acre Access Control’s data query and analytics capabilities. While our high-level API wrapper methods provide convenient access to common operations, aggregates give you direct access to the full power of MongoDB’s query engine—enabling complex queries, real-time analytics, custom reports, and advanced data transformations that would be impossible with simple filters alone.

Why Aggregates Matter

Aggregates are not just a query tool—they’re your bread and butter for building sophisticated access control solutions:

  • Complex Queries - Find people with specific card assignments, access levels, and schedule combinations
  • Real-Time Analytics - Count events by type, analyze access patterns, track system health
  • Custom Reports - Generate compliance reports, audit trails, occupancy statistics
  • Data Transformation - Reshape, enrich, and correlate data across multiple object types
  • Performance - Execute server-side processing for massive datasets without loading everything into memory

Performance Note: Aggregates execute on the MongoDB server, not in your application.

When to Use Aggregates

Use Case Use Aggregate? Alternative
Find all people in a folder ❌ No Use GetPeopleAsync()
Need specific properties of objects ✅ Yes Aggregate with $project
Count total doors in instance ✅ Yes Aggregate with $count
Find people with >3 active cards ✅ Yes Aggregate with $match on array size
Get single person by key ❌ No Use GetPersonAsync(key)
Find access events in last 30 days for specific reader ✅ Yes Aggregate Events collection
List all access levels ❌ No Use GetAccessLevelsAsync()
Generate occupancy report by building/floor ✅ Yes Aggregate with $group and $sum
Find hardware offline >24 hours ✅ Yes Aggregate with date comparison

Collections in acre Access Control

acre Access Control uses two primary MongoDB collections for storing all system data.

KeepObjects Collection

What it contains: All acre Access Control objects except Events

  • Person, User, Person.CardAssignment
  • Hardware (Controller, Reader, Door, Input, Output)
  • Configuration (Access Level, Schedule, BadgeType, Card Format)
  • Folder, Instance, Instance Setting
  • Integration objects (Wavelynx, Engage, Mercury, etc.)

Collection Name: KeepObjects Use For: Finding objects, generating reports, analyzing system configuration

Events Collection

What it contains: All EventMessage objects

  • Access events (granted, denied, card reads)
  • System events (controller online/offline, tamper alarms)
  • Alarm events (door forced, held open)
  • Audit events (object modified, user login)
  • Integration events (external system notifications)

Collection Name: Events
Typical Size: Millions to billions of documents (high-volume, time-series data)
Use For: Event history, access analytics, compliance reporting, real-time monitoring

Aggregation Pipeline Fundamentals

An aggregation pipeline is a sequence of stages that process documents step-by-step, like an assembly line. Each stage transforms the documents and passes them to the next stage.

Pipeline Structure

[
  { "$match": { /* filter criteria */ } },      // Stage 1: Filter documents
  { "$project": { /* field selection */ } },    // Stage 2: Select/transform fields
  { "$sort": { /* sort order */ } },            // Stage 3: Order results
  { "$limit": 10 }                              // Stage 4: Limit results
]

Key Concept: Each stage receives documents from the previous stage and outputs documents to the next stage. This allows you to chain operations for powerful queries.

Pipeline Execution Flow

Documents in Collection
       ↓
    $match (filter to relevant documents)
       ↓
    $unwind (expand arrays)
       ↓
    $group (aggregate/count)
       ↓
    $sort (order results)
       ↓
    $limit (take top N)
       ↓
   Final Results

Essential Aggregation Stages

$match - Filter Documents

Purpose: Select documents that meet specific criteria (like SQL WHERE)

When to use: Always use $match as early as possible to reduce the number of documents processed in subsequent stages.

Basic Match

{
  "$match": {
    "CommonName": "Front Door"
  }
}

Returns: All objects with CommonName exactly “Front Door”

Multiple Conditions (AND)

{
  "$match": {
    "$and": [
      { "_t": "Person" },
      { "IsDeleted": { "$ne": true } }
    ]
  }
}

Returns: All Person objects that are not deleted

OR Conditions

{
  "$match": {
    "$or": [
      { "_t": "MercuryReader" },
      { "_t": "EngageReader" },
      { "_t": "WavelynxReader" }
    ]
  }
}

Returns: All reader objects regardless of hardware type

Field Exists Check

{
  "$match": {
    "CardAssignments": { "$exists": true, "$ne": [] }
  }
}

Returns: Objects that have a non-empty CardAssignments array

Date Range (Events)

{
  "$match": {
    "OccurredOn.Date": {
      "$gte": { "$date": "2026-01-01T00:00:00Z" },
      "$lt": { "$date": "2026-02-01T00:00:00Z" }
    }
  }
}

Returns: All events that occurred in January 2026

Array Element Match

{
  "$match": {
    "ObjectLinks.Relation": "Person",
    "ObjectLinks.LinkedObjectId": { "$oid": "5af040aca3ac160df4ba26dc" }
  }
}

Returns: All objects with an ObjectLink referencing the specified Person

Text Search (Indexed)

{
  "$match": {
    "$text": { "$search": "John Smith" }
  }
}

Returns: All objects matching “John Smith” in indexed text fields (case-insensitive)

$project - Select and Transform Fields

Purpose: Include, exclude, rename, or compute fields (like SQL SELECT)

When to use: Reduce document size for performance, create calculated fields, reshape data

Exclude Fields

{
  "$project": {
    "_id": 0,
    "ParentFolderIds": 0,
    "InternalData": 0
  }
}

Result: All fields except _id, ParentFolderIds, and InternalData

Include Only Specific Fields

{
  "$project": {
    "CommonName": 1,
    "CardAssignments": 1,
    "_id": 0
  }
}

Result: Only CommonName and CardAssignments fields

Rename Fields

{
  "$project": {
    "name": "$CommonName",
    "id": "$_id",
    "folder": "$InFolderKey"
  }
}

Result: Fields renamed to more client-friendly names

Computed Fields

{
  "$project": {
    "CommonName": 1,
    "CardCount": { "$size": { "$ifNull": ["$CardAssignments", []] } },
    "HasCards": { "$gt": [{ "$size": { "$ifNull": ["$CardAssignments", []] } }, 0] }
  }
}

Result: Original name plus computed CardCount and HasCards boolean

$sort - Order Results

Purpose: Sort documents by one or more fields (like SQL ORDER BY)

Sort Values:

  • 1 = Ascending (A-Z, 0-9, oldest-newest)
  • -1 = Descending (Z-A, 9-0, newest-oldest)

Single Field Sort

{
  "$sort": {
    "CommonName": 1
  }
}

Result: Documents sorted alphabetically by CommonName

Multi-Field Sort

{
  "$sort": {
    "Priority": -1,
    "OccurredOn.Date": -1
  }
}

Result: Events sorted by priority (high to low), then by time (newest first)

Sort by Computed Field

[
  {
    "$project": {
      "CommonName": 1,
      "CardCount": { "$size": { "$ifNull": ["$CardAssignments", []] } }
    }
  },
  {
    "$sort": { "CardCount": -1 }
  }
]

Result: People sorted by number of cards (most cards first)

$limit - Restrict Result Count

Purpose: Return only the first N documents (like SQL LIMIT)

When to use: Prevent overwhelming results, implement pagination, get “top N” results

{
  "$limit": 100
}

Result: Only first 100 documents

Top 10 Pattern

[
  { "$match": { "_t": "Person" } },
  { "$sort": { "CommonName": 1 } },
  { "$limit": 10 }
]

Result: First 10 people alphabetically

$skip - Offset Results

Purpose: Skip the first N documents (like SQL OFFSET)

When to use: Implement pagination with $limit

{
  "$skip": 50
}

Result: Skip first 50 documents, return rest

Pagination Pattern

[
  { "$match": { "_t": "Person" } },
  { "$sort": { "CommonName": 1 } },
  { "$skip": 100 },    // Page 6: skip first 100 (pages 1-5)
  { "$limit": 20 }     // Show 20 per page
]

Result: Page 6 of results (records 101-120)

Best Practice: Always use $sort before $skip/$limit to ensure consistent pagination

$group - Aggregate and Summarize

Purpose: Group documents by key and compute aggregates (like SQL GROUP BY)

When to use: Count, sum, average, find min/max, collect arrays

Count Documents

{
  "$group": {
    "_id": null,
    "totalCount": { "$sum": 1 }
  }
}

Result: Single document with total count

Group and Count by Type

{
  "$group": {
    "_id": "$_t",
    "count": { "$sum": 1 }
  }
}

Result: One document per object type with count

Example Output:

[
  { "_id": "Person", "count": 1523 },
  { "_id": "MercuryReader", "count": 245 },
  { "_id": "AccessLevel", "count": 67 }
]

Sum Values

{
  "$group": {
    "_id": "$EventTypeId",
    "totalEvents": { "$sum": 1 },
    "totalPriority": { "$sum": "$Priority" },
    "avgPriority": { "$avg": "$Priority" }
  }
}

Result: Event statistics by type

Collect Values into Array

{
  "$group": {
    "_id": "$InFolderKey",
    "peopleNames": { "$push": "$CommonName" },
    "peopleCount": { "$sum": 1 }
  }
}

Result: For each folder, an array of all person names and the count

Find Min/Max

{
  "$group": {
    "_id": null,
    "earliestEvent": { "$min": "$OccurredOn.Date" },
    "latestEvent": { "$max": "$OccurredOn.Date" },
    "highestPriority": { "$max": "$Priority" }
  }
}

Result: Date range and max priority across all events

$unwind - Expand Arrays

Purpose: Create a document for each array element (denormalize arrays)

When to use: Query array contents, join on array elements, count array items

Basic Unwind

{
  "$unwind": "$CardAssignments"
}

Before:

{
  "_id": "person1",
  "CommonName": "John Smith",
  "CardAssignments": [
    { "DisplayCardNumber": "12345", "EncodedCardNumber": 54321 },
    { "DisplayCardNumber": "67890", "EncodedCardNumber": 09876 }
  ]
}

After:

[
  {
    "_id": "person1",
    "CommonName": "John Smith",
    "CardAssignments": { "DisplayCardNumber": "12345", "EncodedCardNumber": 54321 }
  },
  {
    "_id": "person1",
    "CommonName": "John Smith",
    "CardAssignments": { "DisplayCardNumber": "67890", "EncodedCardNumber": 09876 }
  }
]

Preserve Null/Empty Arrays

{
  "$unwind": {
    "path": "$ObjectLinks",
    "preserveNullAndEmptyArrays": true
  }
}

Result: Documents without ObjectLinks are still included (not dropped)

With Array Index

{
  "$unwind": {
    "path": "$CardAssignments",
    "includeArrayIndex": "cardIndex"
  }
}

Result: Each unwound document includes cardIndex field (0, 1, 2, …)

$keepLookup - Join Collections

Purpose: Combine data from multiple collections (like SQL JOIN)

When to use: Enrich objects with related data, resolve references

Basic Lookup

{
  "$keepLookup": {
    "localField": "ObjectLinks.LinkedObjectId",
    "foreignField": "_id",
    "as": "linkedObjects"
  }
}

Result: Each document includes linkedObjects array with full objects from KeepObjects collection

Parameters

Parameter Required Default Description
localField ✅ Yes N/A Field in current document to match
foreignField ❌ No "_id" Field in KeepObjects collection to match against
as ✅ Yes N/A Output array field name for matched documents
preserveNull ❌ No false Include documents with no matches (like LEFT JOIN)
unwind ❌ No true Automatically unwind the result array
project ❌ No null Project specific fields from matched documents

Preserve Null (LEFT JOIN)

{
  "$keepLookup": {
    "localField": "ObjectLinks.LinkedObjectId",
    "as": "linkedObjects",
    "preserveNull": true
  }
}

Result: Documents without matches are still included with empty linkedObjects array

Without Auto-Unwind

{
  "$keepLookup": {
    "localField": "ObjectLinks.LinkedObjectId",
    "as": "linkedObjects",
    "unwind": false
  }
}

Result: linkedObjects remains an array (not automatically unwound to individual documents)

With Field Projection

{
  "$keepLookup": {
    "localField": "ObjectLinks.LinkedObjectId",
    "as": "linkedObjects",
    "project": {
      "CommonName": 1,
      "_t": 1,
      "Email": 1
    }
  }
}

Result: Matched documents only include CommonName, _t, and Email fields

Working Example

[
  {
    "$match": {
      "ObjectLinks.Relation": "Person"
    }
  },
  {
    "$unwind": "$ObjectLinks"
  },
  {
    "$match": {
      "ObjectLinks.Relation": "Person"
    }
  },
  {
    "$keepLookup": {
      "as": "Person",
      "localField": "ObjectLinks.LinkedObjectId",
      "foreignField": "_id",
      "project": {
        "CommonName": 1,
        "CardAssignments": {
          "$filter": {
            "input": "$CardAssignments",
            "as": "ca",
            "cond": {
              "$and": [
                {
                  "$eq": [
                    "$$ca.IsDisabled",
                    false
                  ]
                },
                {
                  "$gte": [
                    "$$ca.ExpiresOn",
                    "$$NOW"
                  ]
                }
              ]
            }
          }
        }
      }
    }
  },
  {
    "$limit": 100
  }
]

Result: Events enriched with person’s card details

Note: $keepLookup always queries the KeepObjects collection. For performance, use $match before $keepLookup to reduce the number of documents being enriched and include a $project in the keepLookup to reduce data transfer.

Result: Each document includes ObjectLinks array with full object “pointers”

Note: $keepLookup has performance implications on large datasets. Use $match before $keepLookup to reduce document count.

$count - Count Results

Purpose: Return the count of documents (simpler than $group)

{
  "$count": "totalDocuments"
}

Result:

{ "totalDocuments": 1523 }

$facet - Multiple Pipelines

Purpose: Run multiple aggregation pipelines on the same data

When to use: Get multiple statistics in one query (counts, averages, samples)

ALWAYS include a $match step before a $facet in the pipeline

{
  "$facet": {
    "totalCount": [{ "$count": "count" }],
    "topTen": [{ "$sort": { "CommonName": 1 } }, { "$limit": 10 }],
    "statistics": [
      {
        "$group": {
          "_id": null,
          "avgCards": { "$avg": { "$size": { "$ifNull": ["$CardAssignments", []] } } }
        }
      }
    ]
  }
}

Result: Single document with totalCount, topTen, and statistics fields

Common Operators

Comparison Operators

Operator Description Example
$eq Equal to { "Priority": { "$eq": 200 } }
$ne Not equal to { "IsDeleted": { "$ne": true } }
$gt Greater than { "Priority": { "$gt": 50 } }
$gte Greater than or equal { "Priority": { "$gte": 100 } }
$lt Less than { "Priority": { "$lt": 25 } }
$lte Less than or equal { "Priority": { "$lte": 10 } }
$in In array { "_t": { "$in": ["Person", "Attendee"] } }
$nin Not in array { "Status": { "$nin": ["Offline", "Unknown"] } }

Logical Operators

Operator Description Example
$and All conditions must be true { "$and": [{ "a": 1 }, { "b": 2 }] }
$or At least one condition must be true { "$or": [{ "a": 1 }, { "b": 2 }] }
$not Inverts condition { "field": { "$not": { "$eq": 5 } } }
$nor No conditions are true { "$nor": [{ "a": 1 }, { "b": 2 }] }

Element Operators

Operator Description Example
$exists Field exists { "CardAssignments": { "$exists": true } }
$type Field type check { "_id": { "$type": "objectId" } }

Array Operators

Operator Description Example
$size Array size { "$size": "$CardAssignments" }
$in Value in array { "Tags": { "$in": ["vip"] } }
$all All values in array { "Tags": { "$all": ["Employee", "Active"] } }

Real-World Examples

Example 1: Find People with Specific Card Number

Use Case: Locate a cardholder by their encoded card number

[
  {
    "$match": {
      "$and": [ // $and is redundant here
        { "_t": "Person" },
        { "CardAssignments.EncodedCardNumber": 123456 }
      ]
    }
  },
  {
    "$project": {
      "_id": 0,
      "CommonName": 1,
      "CardAssignments": 1
    }
  }
]

C# Wrapper Example:

var pipeline = new BsonDocument[]
{
    new BsonDocument("$match", new BsonDocument
    {
        { "$and", new BsonArray
            {
                new BsonDocument("_t", "Person"),
                new BsonDocument("CardAssignments.EncodedCardNumber", 123456)
            }
        }
    })
};

var results = await client.AggregateAsync("KeepObjects", pipeline, instanceKey);

cURL Example:

curl -X POST 'https://api.us.acresecurity.cloud/api/f/FOLDER_ID/search?page=0&pageSize=50' \
  -H "Authorization: Bearer TOKEN" \
  -H 'Content-Type: application/json' \
  -d '"{$and: [ { _t: \"Person\" }, {\"CardAssignments.EncodedCardNumber\": 123456} ] }"'

Example 2: Count Hardware by Type

Use Case: Generate inventory report of all hardware

[
  {
    "$match": {
      "_t": {
        "$in": [
          "MercuryReader", "MercuryController", "MercuryDownstream",
          "EngageReader", "EngageSite",
          "WavelynxReader", "WavelynxGateway",
          "BoschPanel", "BoschArea"
        ]
      }
    }
  },
  {
    "$group": {
      "_id": {
        "$last": "$_t"
      },
      "count": { "$sum": 1 },
      "devices": { "$push": "$CommonName" }
    }
  },
  {
    "$sort": { "count": -1 }
  }
]

Result:

[
  { 
    "_id": "MercuryReader", 
    "count": 245, 
    "devices": ["Front Door", "Back Door", "Side Entrance", "..."]
  },
  { 
    "_id": "MercuryController", 
    "count": 45, 
    "devices": ["Building A LP1500", "Building B LP4502", "..."]
  },
  { 
    "_id": "EngageReader", 
    "count": 12, 
    "devices": ["Elevator Car 1", "Elevator Car 2", "..."]
  }
]

Example 3: Access Events in Last 30 Days for Specific Reader

Use Case: Generate access report for a specific door

[
  {
    "$match": {
      "EventTypeId": { "$in": [{"$oid": "61cb71500000000000000000"}, {"$oid": "62ab71500000000000000000"} ] },
      "ObjectLinks": {
        "$elemMatch": {
          "LinkedObjectId": { "$oid": "5b2134bca3ac160dd82dd987" },
          "Relation": "Reader"
        }
      },
      "OccurredOn.Date": {
        "$gte": { "$date": "2025-12-22T00:00:00Z" },
        "$lt": { "$date": "2026-01-21T23:59:59Z" }
      }
    }
  },
  {
    "$sort": { "OccurredOn.Date": -1 }
  },
  {
    "$limit": 100
  },
  {
    "$project": {
      "EventTypeId": 1,
      "OccurredOn": 1,
      "MessageLong": 1,
      "Priority": 1,
      "ObjectLinks": 1
    }
  }
]

Example 4: Find People with More Than 3 Active Cards

Use Case: Identify cardholders with excessive card assignments

[
  {
    "$match": {
      "_t": "Person"
    }
  },
  {
    "$project": {
      "CommonName": 1,
      "CardAssignments": 1,
      "cardCount": { "$size": { "$ifNull": ["$CardAssignments", []] } }
    }
  },
  {
    "$match": {
      "cardCount": { "$gt": 3 }
    }
  },
  {
    "$sort": { "cardCount": -1 }
  }
]

Result: People sorted by number of cards (most first), only those with >3 cards

Example 5: Daily Access Event Counts

Use Case: Generate daily access statistics for the past week

[
  {
    "$match": {
      "EventTypeId": {"$oid": "61cb71500000000000000000"},
      "OccurredOn.Date": {
        "$gte": { "$date": "2026-01-14T00:00:00Z" },
        "$lt": { "$date": "2026-01-21T23:59:59Z" }
      }
    }
  },
  {
    "$group": {
      "_id": {
        "$dateToString": { "format": "%Y-%m-%d", "date": "$OccurredOn.Date" }
      },
      "totalEvents": { "$sum": 1 }
    }
  },
  {
    "$sort": { "_id": 1 }
  }
]

Result:

[
  { "_id": "2026-01-14", "totalEvents": 1523 },
  { "_id": "2026-01-15", "totalEvents": 1687 },
  { "_id": "2026-01-16", "totalEvents": 1456 },
  { "_id": "2026-01-17", "totalEvents": 1598 },
  { "_id": "2026-01-18", "totalEvents": 1234 },
  { "_id": "2026-01-19", "totalEvents": 892 },
  { "_id": "2026-01-20", "totalEvents": 1045 }
]

Example 6: Find Doors Currently in Alarm State

Use Case: Security dashboard showing active alarms

[
  {
    "$match": {
      "$and": [
        { "_t": { "$in": ["MercuryDoor", "AscDoor"] } },
        {
          "$or": [
            { "DoorState": "ForcedOpen" },
            { "DoorState": "HeldOpen" },
            { "DoorState": "Propped" }
          ]
        }
      ]
    }
  },
  {
    "$project": {
      "CommonName": 1,
      "DoorState": 1,
      "InFolderKey": 1,
      "_t": 1
    }
  },
  {
    "$sort": { "CommonName": 1 }
  }
]

Example 7: Controllers Offline for More Than 24 Hours

Use Case: Identify hardware requiring maintenance

[
  {
    "$match": {
      "$and": [
        { "_t": { "$in": ["MercuryController", "BoschPanel"] } },
        { "IsOnline": false },
        {
          "LastPing": {
            "$lt": { "$date": "2026-01-20T00:00:00Z" }
          }
        }
      ]
    }
  },
  {
    "$project": {
      "CommonName": 1,
      "MacAddress": 1,
      "LastPing": 1,
      "hoursOffline": {
        "$divide": [
          { "$subtract": [{ "$date": "2026-01-21T00:00:00Z" }, "$LastPing"] },
          3600000
        ]
      }
    }
  },
  {
    "$sort": { "hoursOffline": -1 }
  }
]

Result: Controllers sorted by how long they’ve been offline (worst first)

Example 8: Access Level Usage Statistics

Use Case: Understand which access levels are most used

[
  {
    "$match": {
      "EventTypeId": {"$oid": "61cb71500000000000000000"},
      "OccurredOn.Date": {
        "$gte": { "$date": "2026-01-01T00:00:00Z" }
      }
    }
  },
  {
    "$project":{
      "$ObjectLinks": 1
    }
  },
  {
    "$unwind": "$ObjectLinks"
  },
  {
    "$match": {
      "ObjectLinks.Relation": "AccessLevel"
    }
  },
  {
    "$group": {
      "_id": {
        "accessLevelId": "$ObjectLinks.LinkedObjectId",
        "accessLevelName": "$ObjectLinks.CommonName"
      },
      "usageCount": { "$sum": 1 }
    }
  },
  {
    "$sort": { "usageCount": -1 }
  },
  {
    "$limit": 20
  }
]

Result: Top 20 access levels by usage in January 2026

Example 9: Find People Without Card Assignments

Use Case: Identify incomplete cardholder records

[
  {
    "$match": {
      "$and": [
        { "_t": "Person" },
        {
          "$or": [
            { "CardAssignments": { "$exists": false } },
            { "CardAssignments": { "$size": 0 } }
          ]
        }
      ]
    }
  },
  {
    "$project": {
      "CommonName": 1,
      "GivenName": 1,
      "Surname": 1,
      "InFolderKey": 1
    }
  },
  {
    "$sort": { "CommonName": 1 }
  }
]

Example 10: Occupancy Report by Building

Use Case: Real-time occupancy tracking

[
  {
    "$match": {
      "$and": [
        { "EventTypeId": {"$oid": "61cb71500000000000000000"} },
        { "Monikers.Nickname": "Entry" },
        {
          "OccurredOn.Date": {
            "$gte": { "$date": "2026-01-21T00:00:00Z" }
          }
        }
      ]
    }
  },
  {
    "$project": {
      "_id": 1,
      "ObjectLinks": 1,
      "Metadata": 1
    }
  },
  {
    "$unwind": "$Metadata"
  },
  {
    "$match": {
      "Metadata.Application": "Building"
    }
  },
  {
    "$group": {
      "_id": "$Metadata.Values.SiteId",
      "entryCount": { "$sum": 1 },
      "uniquePeople": { "$addToSet": "$ObjectLinks.LinkedObjectId" }
    }
  },
  {
    "$project": {
      "building": "$_id",
      "totalEntries": "$entryCount",
      "uniqueVisitors": { "$size": "$uniquePeople" }
    }
  },
  {
    "$sort": { "uniqueVisitors": -1 }
  }
]

Result:

[
  { "building": "Building A", "totalEntries": 342, "uniqueVisitors": 156 },
  { "building": "Building B", "totalEntries": 289, "uniqueVisitors": 132 },
  { "building": "Building C", "totalEntries": 178, "uniqueVisitors": 87 }
]

Advanced Patterns

Pattern 1: Pagination with Total Count

Get both results and total count in one query:

[
  {
    "$match": { "_t": "Person" }
  },
  {
    "$facet": {
      "results": [
        { "$sort": { "CommonName": 1 } },
        { "$skip": 40 },
        { "$limit": 20 }
      ],
      "totalCount": [
        { "$count": "count" }
      ]
    }
  }
]

Result:

{
  "results": [
    { "_id": "...", "CommonName": "Alice Johnson", "..." },
    { "_id": "...", "CommonName": "Bob Smith", "..." },
    "... 18 more person objects ..."
  ],
  "totalCount": [{ "count": 1523 }]
}

Pattern 2: Conditional Field Projection

Project different fields based on object type:

[
  {
    "$match": {
      "_t": { "$in": ["Person", "MercuryReader"] }
    }
  },
  {
    "$project": {
      "CommonName": 1,
      "objectType": "$_t",
      "typeSpecificData": {
        "$cond": {
          "if": { "$eq": ["$_t", "Person"] },
          "then": {
            "email": "$Email",
            "cards": { "$size": { "$ifNull": ["$CardAssignments", []] } }
          },
          "else": {
            "mac": "$MacAddress",
            "online": "$IsOnline"
          }
        }
      }
    }
  }
]

Pattern 3: Find Objects by Tag Combination

Find people with specific tag combinations:

[
  {
    "$match": {
      "$and": [
        { "_t": "Person" },
        { "Tags": { "$all": ["employee", "active"] } },
        { "Tags": { "$nin": ["terminated", "on-leave"] } }
      ]
    }
  }
]

Logic: Person must have both “employee” AND “active” tags, but NOT “terminated” or “on-leave”

Pattern 4: Event Correlation

Find all events related to a specific person across different event types:

[
  {
    "$match": {
      "$and": [
        { "ObjectLinks.Relation": "Person" },
        { "ObjectLinks.LinkedObjectId": { "$oid": "PERSON_KEY" } },
        {
          "OccurredOn.Date": {
            "$gte": { "$date": "2026-01-01T00:00:00Z" }
          }
        }
      ]
    }
  },
  {
    "$group": {
      "_id": "$EventTypeId",
      "count": { "$sum": 1 },
      "lastOccurred": { "$max": "$OccurredOn.Date" },
      "sampleEvents": { "$push": { "time": "$OccurredOn.Date", "message": "$MessageLong" } }
    }
  },
  {
    "$sort": { "count": -1 }
  }
]

Result: Summary of all event types for a person with counts and samples

Pattern 5: Time-Series Bucketing

Group events by hour of day:

[
  {
    "$match": {
      "EventTypeId": {"$oid": "61cb71500000000000000000"},
      "OccurredOn.Date": {
        "$gte": { "$date": "2026-01-21T00:00:00Z" },
        "$lt": { "$date": "2026-01-22T00:00:00Z" }
      }
    }
  },
  {
    "$project": {
      "hour": { "$hour": "$OccurredOn.Date" }
    }
  },
  {
    "$group": {
      "_id": "$hour",
      "count": { "$sum": 1 }
    }
  },
  {
    "$sort": { "_id": 1 }
  }
]

Result: Access counts by hour (0-23) for January 21, 2026

Using Aggregates via API

C# Wrapper Method

using Feenics.Keep.WebApi.Wrapper;
using MongoDB.Bson;
using System.Threading.Tasks;

public async Task<BsonDocument[]> GetHardwareInventory(Client client, string instanceKey)
{
    var pipeline = new BsonDocument[]
    {
        new BsonDocument("$match", new BsonDocument
        {
            { "_t", new BsonDocument("$in", new BsonArray
                {
                    "MercuryReader", "MercuryController",
                    "EngageReader", "WavelynxReader"
                })
            }
        }),
        new BsonDocument("$group", new BsonDocument
        {
            { "_id", "$_t" },
            { "count", new BsonDocument("$sum", 1) }
        }),
        new BsonDocument("$sort", new BsonDocument("count", -1))
    };
    
    var results = await client.AggregateAsync("KeepObjects", pipeline, instanceKey);
    return results;
}

C# with AggregateStream (Large Datasets)

For queries returning millions of documents, use streaming:

public async Task ProcessLargeEventSet(Client client, string instanceKey)
{
    var pipeline = new BsonDocument[]
    {
        new BsonDocument("$match", new BsonDocument
        {
            { "EventTypeId", new BsonDocument
                {
                    { "$oid", "61cb71500000000000000000" }
                } 
            },
            { "OccurredOn.Date", new BsonDocument
                {
                    { "$gte", DateTime.UtcNow.AddDays(-90) }
                }
            }
        })
    };
    
    await foreach (var eventDoc in client.AggregateStreamAsync("Events", pipeline, instanceKey))
    {
        // Process each event without loading all into memory
        Console.WriteLine($"Event: {eventDoc["MessageLong"]}");
    }
}

When to use streaming:

  • Results >3,000 documents
  • Memory-constrained environments
  • Real-time processing requirements

cURL Examples

Basic Aggregate via Search Endpoint

curl -X POST 'https://api.us.acresecurity.cloud/api/f/FOLDER_ID/search?page=0&pageSize=100' \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H 'Content-Type: application/json' \
  -d '"{$and: [ { _t: \"Person\" }, {\"CardAssignments.EncodedCardNumber\": 123456} ] }"'

Full Aggregate Pipeline

curl -X POST 'https://api.us.acresecurity.cloud/api/f/FOLDER_ID/aggregate/KeepObjects' \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H 'Content-Type: application/json' \
  -d '[
    { "$match": { "_t": "Person" } },
    { "$project": { "CommonName": 1, "cardCount": { "$size": { "$ifNull": ["$CardAssignments", []] } } } },
    { "$match": { "cardCount": { "$gt": 0 } } },
    { "$sort": { "cardCount": -1 } },
    { "$limit": 10 }
  ]'

MongoDB Field Mapping Reference

Understanding how acre API model properties map to MongoDB fields is essential for writing effective aggregates.

BaseInfo Object Mapping

MongoDB Field MongoDB Type API Model Property Model Type Description
_id ObjectId Key string Unique identifier for the object
_t string[] N/A N/A Object type discriminator (e.g., “Person”, “MercuryReader”)
CommonName string CommonName string User-defined name for the object
InFolderId ObjectId InFolderKey string Parent folder/instance key
InstanceScopeId ObjectId N/A N/A Top-level instance in hierarchy (Enterprise scenarios)
ResourceInstanceId ObjectId N/A N/A Immediate instance containing the object
ParentFolderIds ObjectId[] N/A N/A Full path of folder IDs from root to object
N/A N/A Href string Generated by API from ParentFolderIds
N/A N/A InFolderHref string Generated by API from ParentFolderIds
IsDeleted bool N/A N/A Deprecated - Deleted objects are removed, not flagged
Metadata object[] Metadata MetadataItem[] Custom key-value pairs
Monikers object[] Monikers MonikerItem[] Type/classification tags
Notes object[] Notes NoteInfo[] User comments and annotations
ObjectLinks object[] ObjectLinks ObjectLinkItem[] References to related objects
Tags string[] Tags string[] User-defined tags
IsGlobal bool IsGlobal bool Exposes object to entire instance scope (primarily for AccessLevel)
ConcurrencyVersion long ConcurrencyVersion long? Version number for optimistic concurrency control
N/A N/A Links List<Link> Generated by API for HATEOAS navigation

MetadataItem Structure

Metadata allows custom key-value pairs to be attached to any object. Each MetadataItem has:

Property Type Description
Application string Namespace/application identifier (e.g., “HRSystem”, “BadgePrinting”)
Values string JSON or plain text data (flexible format)
ShouldPublishUpdateEvents bool? If true, updates trigger ObjectModified events (default: false)

Example in MongoDB:

"Metadata": [
  {
    "Application": "HRSystem",
    "Values": "{\"EmployeeID\":\"E12345\",\"Department\":\"Engineering\"}",
    "ShouldPublishUpdateEvents": false
  }
]

MonikerItem Structure

Monikers provide unique names/IDs for objects within specific namespaces, allowing third-party systems to maintain their own identifiers.

Property Type Description
Namespace string Application or system name (e.g., “ActiveDirectory”, “PayrollSystem”)
Nickname string Unique identifier within that namespace

Example in MongoDB:

"Monikers": [
  {
    "Namespace": "ActiveDirectory",
    "Nickname": "john.smith@company.com"
  },
  {
    "Namespace": "PayrollSystem",
    "Nickname": "EMP-2024-001"
  }
]

NoteInfo Structure

Notes are timestamped, user-attributed comments attached to objects.

Property Type Description
Key string Unique identifier for the note
CreatedOn DateTime When the note was created (UTC)
User ObjectLinkItem The user who created the note
NoteText string Plain text content of the note

Example in MongoDB:

"Notes": [
  {
    "Key": "507f1f77bcf86cd799439011",
    "CreatedOn": "2026-01-15T14:30:00Z",
    "User": {
      "LinkedObjectKey": "507f191e810c19729de860ea",
      "CommonName": "Jane Admin",
      "Relation": "User"
    },
    "NoteText": "Card replaced due to damage. Old card returned to security."
  }
]

ObjectLinkItem Structure

ObjectLinks represent relationships between objects (e.g., Person → AccessLevel, Event → Reader).

Property Type Description
Href string Generated API path to the linked object
LinkedObjectKey string Key (ObjectId) of the related object
CommonName string Name of the related object (for display)
Relation string Type of relationship (e.g., “AccessLevel”, “Person”, “Reader”)
MetaDataBson byte[] Optional BSON-encoded metadata about the relationship
AddAsTag bool? If true, adds the relationship as a tag

Common Relation Types:

  • "Person" - Link to a person
  • "AccessLevel" - Access permission assignment
  • "Card" - Card assignment
  • "Reader" - Hardware location
  • "Door" - Physical entry point
  • "Schedule" - Time-based rule
  • "User" - System user reference

Example in MongoDB:

"ObjectLinks": [
  {
    "LinkedObjectKey": "507f191e810c19729de860ea",
    "CommonName": "Building A - Full Access",
    "Relation": "AccessLevel",
    "AddAsTag": false
  },
  {
    "LinkedObjectKey": "507f1f77bcf86cd799439012",
    "CommonName": "Front Door Reader",
    "Relation": "Reader"
  }
]

Common Type Discriminators (_t)

Object Type _t Value(s) Collection Use Case
Person ["Person"] KeepObjects Cardholders, employees, visitors
Access Level ["AccessLevel"] KeepObjects Access permissions configuration
Schedule ["Schedule"] KeepObjects Time-based access rules
Reader ["MercuryReader"], ["EngageReader"], ["WavelynxReader"] KeepObjects Physical access points
Controller ["MercuryController"], ["BoschPanel"] KeepObjects Hardware controllers
Door ["MercuryDoor"], ["AscDoor"] KeepObjects Controlled entry points
Input/Output ["MercuryInput"], ["MercuryOutput"] KeepObjects Sensors and relays
Event ["EventMessage"] Events All system events
Badge Type ["BadgeType"] KeepObjects Credential templates
Card Format ["CardFormat"] KeepObjects Encoding specifications

Event-Specific Fields

MongoDB Field Type Description Example
EventTypeId string Event classification “AccessGranted”, “AccessDenied”
OccurredOn DateTime When event happened (UTC) “2026-01-21T18:46:58Z”
PublishedOn DateTime When published to MQTT “2026-01-21T18:46:59Z”
Priority int Event importance (0-1000) 200 (normal), 800 (alarm)
MessageShort string Brief description “Access Granted”
MessageLong string Detailed description “Card Read Granted: John Smith at Front Door”
EventDataBsonBase64 string Event-specific data (Base64 BSON) Encoded binary data
ObjectLinks object[] Related objects [{ Relation: “Person”, LinkedObjectId: … }]

Performance Optimization

Best Practices

1. Use $match Early

✅ DO:

[
  { "$match": { "_t": "Person" } },     // Filter first
  { "$unwind": "$CardAssignments" },
  { "$group": { ... } }
]

❌ DON’T:

[
  { "$unwind": "$CardAssignments" },    // Expensive operation on all docs
  { "$match": { "_t": "Person" } },     // Filter too late
  { "$group": { ... } }
]

2. Project Early to Reduce Document Size

✅ DO:

[
  { "$match": { "_t": "Person" } },
  { "$project": { "CommonName": 1, "CardAssignments": 1 } },  // Only needed fields
  { "$unwind": "$CardAssignments" }
]

3. Use Indexes

Common Indexed fields (faster queries):

  • _id
  • _t
  • InstanceScopeId
  • ObjectLinks.LinkedObjectId
  • Monikers
  • Tags
  • EventTypeId (Events collection)
  • OccurredOn.Date (Events collection)

4. Limit Result Sets

Always use $limit unless you truly need all results:

[
  { "$match": { ... } },
  { "$sort": { ... } },
  { "$limit": 1000 }     // Prevent runaway queries
]

5. Use $facet for Multiple Aggregations

Instead of running multiple queries:

Always add InstanceScopeId and additional $match parameters before your $facet

{
  "$match": {
    "InstanceScopeId": {
      "$oid": "697451d00000000000000000"
    }
  }
},
{
  "$facet": {
    "peopleCount": [{ "$match": { "_t": "Person" } }, { "$count": "count" }],
    "readerCount": [{ "$match": { "_t": "MercuryReader" } }, { "$count": "count" }],
    "eventCount": [{ "$count": "count" }]
  }
}

Restrictions & Security

Blocked Operators

For security and data integrity, certain MongoDB operators are not allowed:

  • $out - Cannot write to collections
  • $merge - Cannot merge into collections
  • $currentOp - Cannot access system operations
  • $indexStats - Cannot access index statistics
  • $planCacheStats - Cannot access query plans
  • $collStats - Cannot access collection statistics
  • $lookup - use the alternative $keepLookup

Instance Scoping

All aggregates are automatically scoped to your authenticated instance:

  • Queries cannot access objects in other instances
  • Enterprise/VAR hierarchies respect parent-child relationships
  • InFolderId and ResourceInstanceId are enforced server-side
  • Include InstanceScopeId in your search queries and $match to leverage indexes

Query Timeout

  • Default execution time: 60 seconds
  • Maximum execution time: 10 minutes
  • Queries exceeding timeout are automatically terminated
  • Use $limit and indexed fields to prevent timeouts

Troubleshooting

Common Errors

“Pipeline stage not allowed”

Cause: Using a blocked operator (e.g., $out, $merge)
Solution: Remove the blocked stage or use allowed alternatives

“Query exceeded time limit”

Cause: Query too complex or missing indexes
Solution:

  1. Add $match early to reduce document count
  2. Use $limit to cap results
  3. Add $project to reduce document size
  4. Ensure you’re querying indexed fields

“Invalid BSON field name”

Cause: Field name contains special characters or starts with $
Solution: Use dot notation for nested fields: "CardAssignments.EncodedCardNumber"

Empty Results

Causes:

  1. _t value doesn’t match (check exact type name)
  2. Field name typo (MongoDB is case-sensitive)
  3. Wrong collection (KeepObjects vs Events)
  4. Instance scoping (object in different instance)

Debug:

[
  { "$match": { "_t": "Person" } },
  { "$limit": 1 }
]

Start simple and add complexity incrementally.

Additional Resources

Documentation

Tools

Summary

MongoDB aggregation pipelines are the engine that powers acre Access Control’s flexibility and analytics capabilities. By mastering aggregates, you unlock:

  • Complex Queries - Find exactly the data you need across millions of records
  • Real-Time Analytics - Generate insights from events and system state
  • Custom Reports - Build compliance, audit, and operational reports
  • Data Transformation - Reshape and correlate data for third-party integrations
  • Performance - Server-side processing eliminates network overhead

Key Takeaways

✅ Start with $match - Filter early to reduce processed documents
✅ Use indexes - Query on _t, _id, InstanceScopeId, EventTypeId, OccurredOn.Date, Monikers ✅ Always $limit - Cap results to prevent timeouts
✅ Project early - Reduce document size before expensive operations
✅ Test incrementally - Build pipelines stage-by-stage
✅ Use $facet - Get multiple aggregations in one query
✅ Stream large results - Use AggregateStreamAsync for >5K documents

Start building powerful queries with aggregates today! 🚀