Database
[{"_id":"622993ef8b87bc000184114a","currencyCode":"USD","businessDayStartDate":"2022-03-05","hourZoneNumber":1,"summaryName":"AMOUNT_OFF_ITEM_M_DISCOUNT_SUMMARY","shardKey":"ENTERPRISE","occurrence":0,"customerCount":0,"itemQuantity":4,"nodeId":"STORE_DEV_TTEZ","endpointId":"998","businessDayId":"377a433f6a73e31aef39e975831a9f8c","amount":{"$numberDecimal":"4"},"lastModifiedTimestamp":{"$date":"2022-03-10T06:30:15.079Z"},"createTimestamp":{"$date":"2022-03-10T06:00:15.204Z"},"version":2},{"_id":"622993ef8b87bc000184114b","currencyCode":"USD","businessDayStartDate":"2022-03-06","hourZoneNumber":2,"summaryName":"AMOUNT_OFF_TOTAL_DISCOUNT_SUMMARY","shardKey":"ENTERPRISE","occurrence":0,"customerCount":2,"itemQuantity":4,"nodeId":"STORE_DEV_TTEC","endpointId":"997","businessDayId":"377a433f6a73e31aef39e975831a9f8c","amount":{"$numberDecimal":"4"},"lastModifiedTimestamp":{"$date":"2022-03-10T06:30:15.082Z"},"createTimestamp":{"$date":"2022-03-10T06:00:15.223Z"},"version":2},{"_id":"622993ef8b87bc000184114c","currencyCode":"USD","businessDayStartDate":"2022-03-04","hourZoneNumber":1,"summaryName":"TOTAL_SALES_SUMMARY","shardKey":"ENTERPRISE","occurrence":0,"customerCount":4,"itemQuantity":0,"nodeId":"STORE_DEV_TTEC","endpointId":"999","businessDayId":"377a433f6a73e31aef39e975831a9f8c","amount":{"$numberDecimal":"41.86"},"lastModifiedTimestamp":{"$date":"2022-03-10T06:30:15.084Z"},"createTimestamp":{"$date":"2022-03-10T06:00:15.226Z"},"version":2},{"_id":"622993ef8b87bc000184114d","currencyCode":"USD","businessDayStartDate":"2022-03-04","hourZoneNumber":1,"summaryName":"TOTAL_DISCOUNT_SUMMARY","shardKey":"ENTERPRISE","occurrence":0,"customerCount":0,"itemQuantity":0,"nodeId":"STORE_DEV_TTEC","endpointId":"999","businessDayId":"377a433f6a73e31aef39e975831a9f8c","amount":{"$numberDecimal":"4"},"lastModifiedTimestamp":{"$date":"2022-03-10T06:30:15.091Z"},"createTimestamp":{"$date":"2022-03-10T06:00:15.230Z"},"version":2},{"_id":"622993ef8b87bc000184114e","currencyCode":"USD","businessDayStartDate":"2022-03-04","hourZoneNumber":1,"summaryName":"NET_SALES_SUMMARY","shardKey":"ENTERPRISE","occurrence":0,"customerCount":0,"itemQuantity":0,"nodeId":"STORE_DEV_TTEC","endpointId":"999","businessDayId":"377a433f6a73e31aef39e975831a9f8c","amount":{"$numberDecimal":"38.06"},"lastModifiedTimestamp":{"$date":"2022-03-10T06:30:15.093Z"},"createTimestamp":{"$date":"2022-03-10T06:00:15.233Z"},"version":2},{"_id":"622993ef8b87bc000184114f","currencyCode":"USD","businessDayStartDate":"2022-03-04","hourZoneNumber":1,"summaryName":"CASH_PAYMENT_SUMMARY","shardKey":"ENTERPRISE","occurrence":4,"customerCount":0,"itemQuantity":0,"nodeId":"STORE_DEV_TTEC","endpointId":"999","businessDayId":"377a433f6a73e31aef39e975831a9f8c","amount":{"$numberDecimal":"41.86"},"lastModifiedTimestamp":{"$date":"2022-03-10T06:30:15.096Z"},"createTimestamp":{"$date":"2022-03-10T06:00:15.236Z"},"version":2},{"_id":"622993ef8b87bc0001841150","currencyCode":"USD","businessDayStartDate":"2022-03-04","hourZoneNumber":1,"summaryName":"NET_CASH_CONTENT_SUMMARY","shardKey":"ENTERPRISE","occurrence":0,"customerCount":0,"itemQuantity":0,"nodeId":"STORE_DEV_TTEC","endpointId":"999","businessDayId":"377a433f6a73e31aef39e975831a9f8c","amount":{"$numberDecimal":"41.86"},"lastModifiedTimestamp":{"$date":"2022-03-10T06:30:15.099Z"},"createTimestamp":{"$date":"2022-03-10T06:00:15.245Z"},"version":2},{"_id":"622993ef8b87bc0001841151","currencyCode":"USD","businessDayStartDate":"2022-03-04","hourZoneNumber":1,"summaryName":"NET_SALES_SUMMARY_2","shardKey":"ENTERPRISE","occurrence":0,"customerCount":0,"itemQuantity":0,"nodeId":"STORE_DEV_TTEC","endpointId":"999","businessDayId":"377a433f6a73e31aef39e975831a9f8c","amount":{"$numberDecimal":"38.06"},"lastModifiedTimestamp":{"$date":"2022-03-10T06:30:15.103Z"},"createTimestamp":{"$date":"2022-03-10T06:00:15.255Z"},"version":2},{"_id":"622993ef8b87bc0001841152","currencyCode":"USD","businessDayStartDate":"2022-03-04","hourZoneNumber":1,"summaryName":"SALES_SUMMARY","shardKey":"ENTERPRISE","occurrence":0,"customerCount":0,"itemQuantity":8,"nodeId":"STORE_DEV_TTEC","endpointId":"999","businessDayId":"377a433f6a73e31aef39e975831a9f8c","amount":{"$numberDecimal":"38.06"},"lastModifiedTimestamp":{"$date":"2022-03-10T06:30:15.104Z"},"createTimestamp":{"$date":"2022-03-10T06:00:15.266Z"},"version":2},{"_id":"622993ef8b87bc0001841153","currencyCode":"USD","businessDayStartDate":"2022-03-04","hourZoneNumber":1,"summaryName":"TOTAL_TAXES_SUMMARY","shardKey":"ENTERPRISE","occurrence":0,"customerCount":0,"itemQuantity":0,"nodeId":"STORE_DEV_TTEC","endpointId":"999","businessDayId":"377a433f6a73e31aef39e975831a9f8c","amount":{"$numberDecimal":"3.8"},"lastModifiedTimestamp":{"$date":"2022-03-10T06:30:15.106Z"},"createTimestamp":{"$date":"2022-03-10T06:00:15.271Z"},"version":2},{"_id":"622b39cf8b87bc0001841885","currencyCode":"USD","businessDayStartDate":"2022-03-10","summaryName":"TILL_OPEN_SUMMARY","shardKey":"ENTERPRISE","occurrence":1,"customerCount":0,"itemQuantity":0,"nodeId":"STORE_DEV","endpointId":"999","businessDayId":"fdfccaace2a9e745cb3eab50dc811076","amount":{"$numberDecimal":"0"},"lastModifiedTimestamp":{"$date":"2022-03-11T12:00:15.019Z"},"createTimestamp":{"$date":"2022-03-11T12:00:15.019Z"},"version":1},{"_id":"622b39cf8b87bc0001841886","currencyCode":"USD","businessDayStartDate":"2022-03-10","summaryName":"NET_CASH_CONTENT_SUMMARY","shardKey":"ENTERPRISE","occurrence":0,"customerCount":0,"itemQuantity":0,"nodeId":"STORE_DEV","endpointId":"999","businessDayId":"fdfccaace2a9e745cb3eab50dc811076","amount":{"$numberDecimal":"200"},"lastModifiedTimestamp":{"$date":"2022-03-11T12:00:15.023Z"},"createTimestamp":{"$date":"2022-03-11T12:00:15.023Z"},"version":1},{"_id":"622b39cf8b87bc0001841887","currencyCode":"USD","businessDayStartDate":"2022-03-10","summaryName":"TILL_OPEN_CASH_SUMMARY","shardKey":"ENTERPRISE","occurrence":1,"customerCount":0,"itemQuantity":0,"nodeId":"STORE_DEV","endpointId":"999","businessDayId":"fdfccaace2a9e745cb3eab50dc811076","amount":{"$numberDecimal":"200"},"lastModifiedTimestamp":{"$date":"2022-03-11T12:00:15.025Z"},"createTimestamp":{"$date":"2022-03-11T12:00:15.025Z"},"version":1},{"_id":"622993ef8b87bc000184115g","currencyCode":"USD","businessDayStartDate":"2022-03-04","hourZoneNumber":1,"summaryName":"TOTAL_TAXES_SUMMARY","shardKey":"ENTERPRISE","occurrence":0,"customerCount":0,"itemQuantity":0,"nodeId":"STORE_DEV_TTEC","endpointId":"997","businessDayId":"377a433f6a73e31aef39e975831a9f8c","amount":{"$numberDecimal":"3.8"},"lastModifiedTimestamp":{"$date":"2022-03-10T06:30:15.106Z"},"createTimestamp":{"$date":"2022-03-10T06:00:15.271Z"},"version":2}]
Query
db.collection.aggregate([{"$match":{"nodeId":{"$in":["STORE_DEV_TTEC","STORE_DEV_TTEZ"]},"businessDayStartDate":{"$gte":"2022-03-04","$lte":"2022-03-07"}}},{"$sort":{"nodeId":1,"endpointId":1,"hourZoneNumber":1}},{"$group":{"_id":{"nodeId":"$nodeId","endpointId":"$endpointId","hourZoneNumber":"$hourZoneNumber"},"customerCount":{"$sum":"$customerCount"},"itemQuantity":{"$sum":"$itemQuantity"},"amount":{"$sum":"$amount"}}},{"$sort":{"_id.hourZoneNumber":1}},{"$group":{"_id":{"nodeId":"$_id.nodeId","endpointId":"$_id.endpointId"},"hourZones":{"$push":{"hourZoneNumber":"$_id.hourZoneNumber","customerCount":{"$sum":"$customerCount"},"itemQuantity":{"$sum":"$itemQuantity"},"amount":{"$sum":"$amount"}}},hourZoneKey:{$first:"$_id.hourZoneNumber"}}},{"$sort":{"_id.endpointId":1}},{"$group":{"_id":"$_id.nodeId","endpoints":{"$push":{"endpointId":"$_id.endpointId","hourZones":"$hourZones"}},endpointKey:{$first:"$_id.endpointId"},hourZoneKey:{$first:"$hourZoneKey"}}},{"$sort":{"nodeId":1,"endpointKey":1,"hourZoneKey":1}},{$project:{_id:0,nodeId:"$_id",endpoints:1,hourZones:1,total:1}}])