In a previous post we examined the aggregation pipeline required to calculate the average number of readers per controller. Additionally it may interesting to know the average number of readers per controller per instance. We’ll use the original aggregation pipeline query as our starting point (download)

We need the Instance Identifier as part of the group by controller so let’s modify the first $group operator to include the InstanceScopeId property:

{
    "$group":{ "_id":{"instanceScopeId":"$InstanceScopeId","controllerId":"$ObjectLinks.LinkedObjectId"},"count":{"$sum":1}}
}

Now our pipeline data stream will look something like:

{"_id":{"instanceScopeId":{"$oid":"5d701b531f151e08acd58488"},"controllerId":{"$oid":"5d701b5ee8760902c44f2817"}},"count":5}
{"_id":{"instanceScopeId":{"$oid":"5d701b531f151e08acd58488"},"controllerId":{"$oid":"5d701b5fec68ee0e201e1018"}},"count":16}
{"_id":{"instanceScopeId":{"$oid":"5be351eb54a65502380576a8"},"controllerId":{"$oid":"5d7012dff848800428716d3c"}},"count":4}
{"_id":{"instanceScopeId":{"$oid":"5c9bac06aa108a03140c3993"},"controllerId":{"$oid":"5d6ff8891f151e08acd4c3cf"}},"count":1}
{"_id":{"instanceScopeId":{"$oid":"5c9bac06aa108a03140c3993"},"controllerId":{"$oid":"5d6fe98d1f151e08acd47d5b"}},"count":1}

From this it’s a fairly straight forward modification of our final $group aggregation to group on the _id.instanceScopeId property rather than on the whole set (null). However we will also want to know which instance has the highest density of readers per controller, and so we need to add a $sort operator.

The updated final $group and the new $sort operators will be:

{
    "$group":{"_id":"$_id.instanceScopeId", "average":{"$avg":"$count"}}
},
{
    "$sort":{"average":-1}
}