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:05.000Z"),"updatedAt":ISODate("2020-03-19T18:21:05.000Z")},{"_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-20T18:16:50.000Z"),"updatedAt":ISODate("2020-03-20T18:16:50.000Z")},]}

db.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:{$ceil:{$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:{$let:{vars:{parsedDate:{$dateFromString:{dateString:"$_id.dateHour",format:"%Y-%m-%dT%H"}}},in:{$add:["$$parsedDate",{$multiply:["$_id.minuteBucket",900000]}]}}},minuteBucket:"$_id.minuteBucket",avgZoneCountNumber:1,avgZoneCountNumberInstant:1,companyName:"$_id.companyName",_id:0,createdAt:new Date()}}])