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": "david@gmail.com",
"count": 10
},
{
"_id": "5fbcbe855e26df3af08ffcee",
"name": "Vincent",
"email": "Vincent@gmail.com",
"count": 7
},
{
"_id": "5fbcb990cb35042db064b2b0",
"name": "robert",
"email": "robert@gmail.com",
"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": ""
}