Database
db={"account":[{"_id":ObjectId("5e1fe45cd05bfb0cc549297d"),"apiCallCount":0,"email":"info@data.com","password":"dummy","userName":"AAAAA","companyName":"The AAAAAA","apiKey":"5e1fe45cd05bfb0cc549297c","solutionType":"VVVVVV","parentCompany":"","buildings":[{"_id":ObjectId("5e1fe5e3d05bfb0cc5494146"),"buildingName":"xxxxxx","address":"xxx","suite":"101","floor":"22","timeZone":"us/eastern","gateways":[{"_id":ObjectId("5e1fe624d05bfb0cc549453a"),"gatewayName":"CC-GW-THF-001","gatewayKey":"gk_5e1fe624d05bfb0cc549453a","suite":"area1","devices":[{"_id":ObjectId("5e1fe751d05bfb0cc549578d"),"serialNumber":"129300000013","area":"area1","connectionStatus":1,"gatewayKey":"gk_5e1fe624d05bfb0cc549453a","applicationNumber":30,"firmwareVersion":"1.0","needsAttention":false,"verificationCode":"GAAS","createdAt":ISODate("2020-01-16T04:32:17.899Z"),"updatedAt":ISODate("2020-01-16T08:53:54.460Z")}],"createdAt":ISODate("2020-01-16T04:27:16.678Z"),"updatedAt":ISODate("2020-01-16T08:53:54.460Z")},{"_id":ObjectId("5e1fe651d05bfb0cc54947f0"),"gatewayName":"AA-GW-THF-002","gatewayKey":"gk_5e1fe651d05bfb0cc54947f0","suite":"area2","devices":[{"_id":ObjectId("5e1fe7a9d05bfb0cc5495cdf"),"serialNumber":"129300000012","area":"area2","connectionStatus":0,"gatewayKey":"gk_5e1fe651d05bfb0cc54947f0","applicationNumber":30,"firmwareVersion":"1.0","needsAttention":false,"verificationCode":"VG3K","createdAt":ISODate("2020-01-16T04:33:45.698Z"),"updatedAt":ISODate("2020-01-16T08:54:17.604Z")}],"createdAt":ISODate("2020-01-16T04:28:01.532Z"),"updatedAt":ISODate("2020-01-16T08:54:17.604Z")},],"createdAt":ISODate("2020-01-16T04:26:11.941Z"),"updatedAt":ISODate("2020-01-16T08:56:32.657Z")}],"createdAt":ISODate("2020-01-16T04:19:40.310Z"),"updatedAt":ISODate("2020-04-06T18:18:39.628Z"),"__v":1,}],"sensingresults":[{"_id":ObjectId("5e73b83e0fa16c0e5a70be90"),"accountId":ObjectId("5e1fe45cd05bfb0cc549297d"),"buildingId":ObjectId("5e1fe5e3d05bfb0cc5494146"),"gatewayId":ObjectId("5e1fe6c5d05bfb0cc5494f09"),"deviceId":ObjectId("5e1fe857d05bfb0cc5496770"),"presenceStatus":0,"zoneCountNumberInstant":0,"zoneCountNumber":0,"longTermIndex":0.85,"shortTermIndex":0.8,"programVersion":"30207","radarMappingValue":4,"environmentMappingValue":1.09,"__v":0,"createdAt":ISODate("2020-03-19T18:21:50.510Z"),"updatedAt":ISODate("2020-03-19T18:21:50.510Z")},{"_id":ObjectId("5e73b83e0fa16c0e5a70be93"),"accountId":ObjectId("5e1fe45cd05bfb0cc549297d"),"buildingId":ObjectId("5e1fe5e3d05bfb0cc5494146"),"gatewayId":ObjectId("5e1fe651d05bfb0cc54947f0"),"deviceId":ObjectId("5e1fe7a9d05bfb0cc5495cdf"),"presenceStatus":0,"zoneCountNumberInstant":0,"zoneCountNumber":0,"longTermIndex":1.5,"shortTermIndex":1.51,"programVersion":"30207","radarMappingValue":2.73,"environmentMappingValue":2.22,"__v":0,"createdAt":ISODate("2020-03-19T18:21:50.766Z"),"updatedAt":ISODate("2020-03-19T18:21:50.766Z")},{"_id":ObjectId("5e73b83f0fa16c0e5a70be9b"),"accountId":ObjectId("5e1fe45cd05bfb0cc549297d"),"buildingId":ObjectId("5e1fe5e3d05bfb0cc5494146"),"gatewayId":ObjectId("5e1fe6a6d05bfb0cc5494d25"),"deviceId":ObjectId("5e1fe81ed05bfb0cc5496406"),"presenceStatus":0,"zoneCountNumberInstant":0,"zoneCountNumber":0,"longTermIndex":1.38,"shortTermIndex":1.41,"programVersion":"30207","radarMappingValue":0.2,"environmentMappingValue":1.41,"__v":0,"createdAt":ISODate("2020-03-19T18:21:51.906Z"),"updatedAt":ISODate("2020-03-19T18:21:51.906Z")},{"_id":ObjectId("5e73b8400fa16c0e5a70be9d"),"accountId":ObjectId("5e1fe45cd05bfb0cc549297d"),"buildingId":ObjectId("5e1fe5e3d05bfb0cc5494146"),"gatewayId":ObjectId("5e1fe689d05bfb0cc5494b50"),"deviceId":ObjectId("5e1fe7f9d05bfb0cc54961b7"),"presenceStatus":0,"zoneCountNumberInstant":0,"zoneCountNumber":0,"longTermIndex":4.34,"shortTermIndex":4.42,"programVersion":"30207","radarMappingValue":4.5,"environmentMappingValue":6.05,"__v":0,"createdAt":ISODate("2020-03-19T18:21:52.036Z"),"updatedAt":ISODate("2020-03-19T18:21:52.036Z")},{"_id":ObjectId("5e73b8400fa16c0e5a70bea3"),"accountId":ObjectId("5e1fe45cd05bfb0cc549297d"),"buildingId":ObjectId("5e1fe5e3d05bfb0cc5494146"),"gatewayId":ObjectId("5e1fe6c5d05bfb0cc5494f09"),"deviceId":ObjectId("5e1fe857d05bfb0cc5496770"),"presenceStatus":0,"zoneCountNumberInstant":0,"zoneCountNumber":0,"longTermIndex":0.86,"shortTermIndex":0.81,"programVersion":"30207","radarMappingValue":4,"environmentMappingValue":1.09,"__v":0,"createdAt":ISODate("2020-03-19T18:21:52.826Z"),"updatedAt":ISODate("2020-03-19T18:21:52.826Z")}]}
Query
db.getCollection("sensingresults").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()
}
}
])