db={"qr":[{"qrId":"0PRA","owner":"1","qrName":"Campaign 0PRA","qrCategory":"html","shortUrl":"http://someurl.com/0PRA"},{"qrId":"NQ17","owner":"2","qrName":"Campaign NQ17","qrCategory":"menu","shortUrl":"http://someurl.com/NQ17"}],"data":[{"_id":"6200f2a8c0cf7a1c49233c7f","qrId":"0PRA","device":"iOS","city":"Beijing","date":ISODate("2021-09-01T11:23:25.184Z")},{"_id":"6200f2eac0cf7a1c49233c80","qrId":"0PRA","device":"AndroidOS","city":"Beijing","date":ISODate("2021-09-01T11:23:25.184Z")},{"_id":"6200f3a4c0cf7a1c49233c81","qrId":"0PRA","device":"AndroidOS","city":"Beijing","date":ISODate("2021-09-01T11:23:25.184Z")},{"_id":"6200f632c0cf7a1c49233c88","qrId":"0PRA","device":"AndroidOS","city":"Nanchang","date":ISODate("2021-09-01T11:23:25.184Z")},{"_id":"6201b342c0cf7a1c49233caa","qrId":"0PRA","device":"iOS","city":"Taizhou","date":ISODate("2021-09-01T11:23:25.184Z")},{"_id":"6200f207c0cf7a1c49233c7a","qrId":"NQ17","device":"iOS","city":"Singapore","date":ISODate("2021-09-01T11:23:25.184Z")},{"_id":"8200f207c1cf7a1c49233c7a","qrId":"NQ17","device":"iOS","city":"Singapore","date":ISODate("2021-09-01T11:23:25.184Z")},{"_id":"6200ac5db44f23b9ec2b6040","qrId":"NQ17","device":"AndroidOS","city":"San Antonio","date":ISODate("2021-09-01T11:23:25.184Z")}]}

db.qr.aggregate([{"$match":{owner:{"$in":["1","2"]}}},{"$lookup":{"from":"data","localField":"qrId","foreignField":"qrId","as":"data","pipeline":[{"$match":{"$and":[{"date":{"$gte":ISODate("2021-09-01T01:23:25.184Z")}},{"date":{"$lte":ISODate("2021-09-02T11:23:25.184Z")}}]}},{"$facet":{"deviceGroup":[{"$group":{"_id":"$device","sum":{"$sum":1}}},{"$sort":{sum:-1}},{"$limit":1}],"cityGroup":[{"$group":{"_id":"$city","sum":{"$sum":1}}},{"$sort":{sum:-1}},{"$limit":1}],"all":[]}}]}},{"$set":{"data":{"$first":"$data.all"},"topDevice":{"$first":{"$first":"$data.deviceGroup._id"}},"topLocation":{"$first":{"$first":"$data.cityGroup._id"}}}},{$group:{_id:{"qrId":"$qrId","qrName":"$qrName","qrCategory":"$qrCategory","shortUrl":"$shortUrl"},data:{$push:{dataItems:"$data",topDevice:"$topDevice",topLocation:"$topLocation",count:{$size:{"$ifNull":["$data",[]]}}}}}}])