Using Aggregates

The aggregate endpoint and aggregate stream provide low-level access to the MongoDB query engine. Detailed exploration of the MongoDb aggregation framework is beyond the scope of this document, please consult MongoDb Documentation for full documentation. Note, certain operators that could be used to access data outside instance hierarchy, or which could compromise the integrity of the system are blocked.

What is an Aggregate

An aggregate pipeline is a collection of operations used to select, aggregate, and project a set of objects from a MongoDB collection. Those familiar with SQL based environments, will recognize the aggregation pipeline as analogous to the SELECT statement.

$match

The key word match in an aggregate is how you find something in the collection you are searching. It allows you to discover items that meet the requirements of the match.

MongoDB Aggregation Match

For example: This will find all items where the common name attribute is blank.

{
    $match:
    {
        CommonName:""
    }
}

$project

Using a project is a good idea to thin out not needed properties. It allows you to select which attribute to pass on to the next stage.

MongoDB Aggregation Project

For example: This will suppress the _id attribute which displays unless specified not to. This also will cause the rest of the attribute to be forwarded on to the next stage of the aggregate.

{
    $project:
    {
        _id:0
    }
}

For example: This will only forward the common name attribute and the _id attribute.

{
    $project:
    {
        CommonName:1
    }
}

$sort

Sort is used to organize the data retrieved into a predictable manner. Many field name and order pairs can be added to one sort. The sort order is determined by 1 or -1. Where one is ascending order and -1 is descending.

MongoDB Aggregation Sort

For example: This will sort all items by the field CommonName in ascending order and sort the items by MacAddress in descending order.

{
    $sort:
    {
        CommonName:1,
        MacAddress:-1
    }
}

$limit

Limit is used to determine how many items to send to the next stage.

MongoDB Aggregation Limit

For example: This will only send 1 item to the next pipeline stage.

{
    $limit:
    {
        1
    }
}

$skip

Skip is used to ignore the first specified number of items. Using skip and limit you can manually create a paging system. we also recommend using sort to reduce the chance of not getting duplicates or missing items, if you are setting up a staging system.

MongoDB Aggregation Skip

For example: This will ignore the first 10 items and send the rest to the next pipeline stage.

{
    $skip:
    {
        10
    }
}

$unwind

An unwind will take an array and make a duplicate item for every entry in the array. The field the array itself was in will no longer be an array and each duplicate item the unwind produced will have a unique way entry in its place.

MongoDB Aggregation Unwind

For example: The following will take a single item and create a new item for every entry in the ObjectLinks field. This will also preserve any item that has null for object links by forwarding them along the pipeline. The las bit places a new field that tells you what position in the array the entry was in.

{
    $unwind:
    {
        path: "$ObjectLinks",
        includeArrayIndex: "IndexArray",    /*Optional*/
        preserveNullAndEmptyArrays: true    /*Optional*/ 
    }
}

Results of the above unwind

/*Initial_Item*/
{
    _id:ObjectId("ID"),
    CommonName:"Bill",
    ObjectLinks:
    {
        Link 1,
        Link 2
    }
}

/*Resulting_Items*/
{

    _id:ObjectId("ID"),
    CommonName:"Bill",
    ObjectLinks: Link 1,
    IndexArray:0
}
{
    _id:ObjectId("ID"),
    CommonName:"Bill",
    ObjectLinks: Link 2,
    IndexArray:1
}

$group

Group is typically used to collate items into a new way of displaying data. It creates unique collections of similar items.

MongoDB Aggregation Group

For example: This will group all types together and provide a total count of how many types there are. Note the type attribute needs to have unwind used on it first for this example to work.

{
    $group:
    {
        _id: "$_id",
        CountOfTypes: 
        {
            $sum: 1
        }
    }
}

$and

An $and will ensure that whatever item this is operating on will only be true if it has both of the items in the $and.

MongoDB And Operator

For example: An Object Link must have a relation of ‘Person’ and a specific person’s object key/Id.

{
    $and: 
    [
        { "ObjectLinks.Relation" : "Person" }, 
        { "ObjectLinks.LinkedObjectId" : ObjectId("KEY_OF_PERSON") }
    ]
}

$or

An $or will ensure that whatever item this is operating on will only be true if it has either of the items in the $or.

MongoDB Or Operator

For example: The _t (type) can be either a ‘Person’ or an ‘Attendee’.

{
    $or : 
    [
        { _t : "Person" },
        { _t : "Attendee" }
    ] 
}

$in

An $in will ensure that whatever item this is operating on will only be true if it has any of the items in the array for the $in. This is very similar to an $or and thats because it is a slightly more optimized way to do several equality checks at once.

MongoDB In Operator

For example: Will select all the items that have the hardware type names in the _t

{
    _t:
    {
        "$in":
        [
          "MercuryReader",
          "MercuryDownstream",
          "MercuryInput",
          "MercuryOutput",
          "MercuryController",
          "BoschPanel",
          "BoschArea",
          "BoschPoint",
          "BoschOutput", 
          "EngageReader", 
          "EngageSite" 
        ]
    }
}

How to Use Aggregates and Keep

Now that we are familiar with MongoDB’s Aggregates we can look at how to use them with Keep’s database.

The best place to start is probably with a quick overview of the databases structure. Particularly the two collections that will interest you.

Keep Objects Collection

The first one we will discuss is the ‘KeepObjects’ collection. This is the collection where all Keep Objects are stored except for any object of the specific type of ‘EventMessage’. So if you would like to find a person, a controller, or something similar this is the collection you would use.

If you want to query the ‘KeepObjects’ please use the ‘KeepObjects’ as the collectionName attribute in the calls that can be found at: Aggregate Wrapper Methods

Events Collection

The second one we will discuss is the ‘Events’ collection. This is the collection where all Keep Objects of type ‘EventMessage’ are stored. So if you would like to find a when a person logged in, a controller came on-line, or something similar this is the collection you would use.

If you want to query the ‘Events’ please use the ‘Events’ as the collectionName attribute in the calls that can be found at: Aggregate Wrapper Methods

Keep Object Structure

Now that we know where to find the objects that we want we should take a look at the structure. We are going to go over a BaseInfo object and compare each attribute to how it is labeled in the data base. Objects with other properties will be configured similar but with a few extra attributes that may be special to just themselves. We will also note that all Keep Objects are based off of Item but since it just has and Href that is not helpful for our database example.

Here’s an example of an API SearchAsync to find any PersonInfo that also has a CardAssignmentInfo with an EncodedCardNumber of 123456

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

Here’s an example of an indexed API SearchAsync to find any objects that have the string “Rick”. This would include results where an object contains “rick” anywhere and the search is case insensitive.

curl -X POST 'https://api.us.acresecurity.cloud/api/f/FOLDER_ID/search?page=0&pageSize=50&includeChildFolders=true&spanScope=true' \
  -H "Authorization: Bearer TOKEN_HERE" \
  -H 'Content-Type: application/json' \
  -d '"{$and: [ { $text: { $search: \"Rick\" } } ] }"'

BaseInfo

Firstly in the database a BaseInfo Object is actually a ‘KeepObject’.

Keep Object MongoDB MongoDB Type Keep Object Client Model Model Type Explanation
_id ObjectId Key String This is the unique identifier for the object.
_t String Array N/A N/A This is how the api knows which object in the database maps to which object in the client object model.
CommonName String CommonName String This is the name the user wishes to store on the object.
InFolderId ObjectId InFolderKey String This is the unique identifier of the folder the object is supposed to reside in.
InstanceScopeId ObjectId N/A N/A This is unique identifier of the top most instance that the object resides in. (Enterprise Hierarchy)
ResourceInstanceId ObjectId N/A N/A This is unique identifier of the immediate instance that the object resides in.
ParentFolderIds ObjectId Array N/A N/A This is how all the unique identifiers for all folders and instances that it takes to route to the object. (Build an Href)
N/A N/A InFolderHref String The Href Items are made by the api and appended to the object during retrieval. It is made up of the ids found in the ParentFolderIds attribute.
N/A N/A Href String The Href Items are made by the api and appended to the object during retrieval. It is made up of the ids found in the ParentFolderIds attribute.
IsDeleted Boolean N/A N/A This flag is only used to identify if an object has been ‘deleted’ from keep. Please note that this is no longer used by us. If you delete an object we no longer keep it in the database, the item is removed and can never be found again.
N/A N/A Links List<Link> The link list is made by the api to determine what other objects it can be associated with. this is not in the database.
Metadata Object Array Metadata MetadataItem[] This is stored as an array of objects and the api turns it into an array of MetaDataItems.
Monikers Object Array Monikers MonikerItem[] This is stored as an array of objects and the api turns it into an array of MonikerItems.
Notes Object Array Notes NoteInfo[] This is stored as an array of objects and the api turns it into an array of NoteInfos.
ObjectLinks Object Array ObjectLinks ObjectLinkItem[] This is stored as an array of objects and the api turns it into an array of ObjectLinkItems.
Tags String Array Tags String[] This is stored and represented the same in the database and in the object model

For further investigation on how a Keep Object looks please use your new skills and use an aggregate to find it; so you can investigate other more complicated objects.