I have collection named "report" like this:
{ "_id" : ObjectId("5fc51722d6827f3bfd24e3b0"), "is_deleted" : false, "reporter" : ObjectId("5fb7b85f516b9709af5c7bc2"), "violator" : ObjectId("5fb8a07e9cd2840f5f6bac5a"), "reportNote" : "vi pham", "status" : 0, "createdAt" : ISODate("2020-11-30T16:00:34.013Z"), "updatedAt" : ISODate("2020-11-30T16:00:34.013Z"), "__v" : 0 } With "reporter" and "violator" is ObjectID that reference from "User" collection Now I want to find a list of violator and re-oder it from larger to small, so I do like this.
db.report.aggregate([ { $group: { _id: "$violator", count: { $sum: 1 } } }, { $sort: { count: -1 } } ]) And I have result as below.
{ "data": [ { "_id": "5fb8a07e9cd2840f5f6bac5a", "count": 10 }, { "_id": "5fbcbe855e26df3af08ffcee", "count": 7 }, { "_id": "5fbcb990cb35042db064b2b0", "count": 6 } ], "total": 23, "message": "" } My expected result is
{ "data": [ { "_id": "5fb8a07e9cd2840f5f6bac5a", "name": "David", "email": "[email protected]", "count": 10 }, { "_id": "5fbcbe855e26df3af08ffcee", "name": "Vincent", "email": "[email protected]", "count": 7 }, { "_id": "5fbcb990cb35042db064b2b0", "name": "robert", "email": "[email protected]", "count": 6 } ], "total": 23, "message": "" } I did follow turivishal recommend.
db.report.aggregate([ { $group: { _id: "$violator", count: { $sum: 1 } } }, { $sort: { count: -1 } }, { $lookup: { from: "users", localField: "violator", foreignField: "_id", as: "ViolatorDetail" } } ]) But the result of ViolatorDetail (User) is empty.
{ "data": [ { "_id": { "violator": "5fb8a07e9cd2840f5f6bac5a", "status": 0, "reportNote": "vi pham" }, "count": 10, "ViolatorDetail": [] }, { "_id": { "violator": "5fbcbe855e26df3af08ffcee", "status": 0, "reportNote": "vi pham" }, "count": 7, "ViolatorDetail": [] }, { "_id": { "violator": "5fbcb990cb35042db064b2b0", "status": 0, "reportNote": "vi pham" }, "count": 6, "ViolatorDetail": [] } ], "total": 23, "message": "" } 