0

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": ""
}
cauchuyennhocuatoi
  • 461
  • 3
  • 8
  • 21
  • Does this answer your question? [how to use populate and aggregate in same statement?](https://stackoverflow.com/questions/16680015/how-to-use-populate-and-aggregate-in-same-statement) – Molda Dec 03 '20 at 07:14
  • Thanks for reply but I did not find the answer in my question. – cauchuyennhocuatoi Dec 03 '20 at 07:34
  • @cauchuyennhocuatoi your question is not clear, can you elaborate and add expected result. – turivishal Dec 03 '20 at 07:51
  • @turivishal: Sorry for my english, I already edit my question and my expected result. Please take a look – cauchuyennhocuatoi Dec 03 '20 at 08:15
  • just do [$lookup](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#equality-match) after $sort stage and $unwind that lookup result. – turivishal Dec 03 '20 at 08:22
  • I just did like you recommend but not work. The user info is empty. I edited in my question, please take a look – cauchuyennhocuatoi Dec 03 '20 at 08:59
  • just look at your result from $group stage you don't have any `violator` field and you are doing this `localField: "violator",` you need to do this `localField: "_id",` – turivishal Dec 03 '20 at 09:06
  • No, violator is field of "Report" collection that I already mention in my question. violator: {type: mongoose.Schema.ObjectId, ref: 'User'}, – cauchuyennhocuatoi Dec 03 '20 at 09:14
  • I know that, i am saying that after $group stage it will no longer available, because you are grouping by that field and value of `violator` field move to `_id` field in group stage(_id: "$violator"), i think you need to learn more about aggregation. – turivishal Dec 03 '20 at 09:25
  • Oh, I missunderstood, thanks you – cauchuyennhocuatoi Dec 03 '20 at 09:32

0 Answers0