Database
[{"collection":"collection","count":10,"content":{"k":{"type":"int","minInt":0,"maxInt":10}}}]
Query
db.getCollection("test").aggregate([
{
$lookup:{
from:"account",
localField:"accountId",
foreignField:"_id",
as:"company_name"
}
},
{$unwind:"$company_name"},
{
$project:{
dateHour:{
$dateToString:{
format:"%Y-%m-%dT%H",
date:"$updatedAt"
}
},
minuteBucket:{
$trunc:{
$divide:[
{
$minute:"$updatedAt"
},
15.0
]
}
},
accountId:1,
buildingId:1,
gatewayId:1,
deviceId:1,
zoneCountNumber:1,
applicationNumber:1,
presenceStatus:1,
zoneCountNumberInstant:1,
longTermIndex:1,
shortTermIndex:1,
programVersion:1,
radarMappingValue:1,
environmentMappingValue:1,
companyName:"$company_name.companyName",
}
},
{
$group:{
_id:{
accountId:"$accountId",
buildingId:"$buildingId",
gatewayId:"$gatewayId",
deviceId:"$deviceId",
applicationNumber:"$applicationNumber",
dateHour:"$dateHour",
minuteBucket:"$minuteBucket",
companyName:"$companyName",
},
avgZoneCountNumber:{
$avg:"$zoneCountNumber"
},
avgZoneCountNumberInstant:{
$avg:"$zoneCountNumberInstant"
},
}
},
{
$project:{
accountId:"$_id.accountId",
buildingId:"$_id.buildingId",
gatewayId:"$_id.gatewayId",
deviceId:"$_id.deviceId",
dateHour:"$_id.dateHour",
minuteBucket:"$_id.minuteBucket",
avgZoneCountNumber:1,
avgZoneCountNumberInstant:1,
companyName:"$_id.companyName",
_id:0,
createdAt:new Date()
}
}
])