How to do group in aggregate but also show other f

2020-07-20 06:08发布

问题:

I need to run group twice to find the post with the highest average likes in comments. Below is my initial stage of my query.

db.posts.aggregate([
    {"$unwind": "$comments"},
    {"$match":
        {
            "$comments.type": {
                "$ne" : "spam"
            },
        }
    }
])

This is what I see after running the query above.

    {
        "_id" : ObjectId("50b59cd75bed76f46522c465"),
        "comment_id" : 49,
        "post_id" : 29,
        "likes" : {
            "type" : "accepted",
            "like" : 3
        }
    },
    {
        "_id" : ObjectId("50b59cd75bed76f46522c465"),
        "comment_id" : 49,
        "post_id" : 29,
        "likes" : {
            "type" : "rejected",
            "like" : 7
        }
    }

What I want to do now is find the average likes a particular comment gets out of these valid records first and then within each post, sum up all these average likes per comment and then divide by total number of comments each post has.

Note that the comment_id is only unique within same post_id. Meaning to say, there are records that are post_id 28, comment_id 49.

I tried this query.

db.posts.aggregate([
    {"$unwind": "$comments"},
    {"$match":
        {
            "$comments.type": {
                "$ne" : "spam"
            },
        }
    },
    {"$group" :
        {
            "_id": "$_id",
            "comment_avg":
            {
                "$avg":"$comments.like"
            }
        }
    }])

I get back the following:

{
            "_id" : ObjectId("50b59cd75bed76f46522c44d"),
            "comment_avg" : 61.074253191058865
        },
        {
            "_id" : ObjectId("50b59cd75bed76f46522c34e"),
            "comment_avg" : 46.82622896256565
        }

As you can see I have lost the post_id information. I have tried $project, but I think I must be doing this wrong.

回答1:

You have not posted you initial document structure.

Document Structure:

{
    "_id" : ObjectId("50b59cd75bed76f46522c471"),
    "comment_id" : 61,
    "post_id" : 29,
    "comments" : [
                   {
                       "type" : "accepted",
                       "like" : 3
                   },
                   {
                      "type" : "rejected",
                      "like" : 3
                   },
                   {
                      "type" : "spam",
                      "like" : 3
                   }
                ]
}

Assuming your document structure as above, i have composed this query. You have to manipulate it as per your needs.

db.posts.aggregate([
        {$unwind:"$comments"},
        {$match:{"$comments.type":{$ne:"spam"}}},
        {$group:{_id:{post_id:"$post_id",comment_id:"$comment_id"},LikeSum:{$sum:"$comments.like"}}},
        {$group:{_id:{post_id:"$_id.post_id"},AvgComments:{$avg:"$LikeSum"}}},
        {$sort:{AvgComments:-1}},
        {$limit:1}
              ])

The above query is constructed as follows:

1.) Unwind the comments array and form individual documents for each element in the comments array
2.) Select only the non-spam comments
3.) Calculate the sum of likes for each comment of all posts
4.) Calculate the average Comment likes for each post
5.) Sort documents in descending order of Average Comment Likes
6.) Select only the first document.

The output document will be something like

{
    "result" : [
        {
            "_id" : {
                       "post_id" : xx
                    },
            "AvgComments" : xx.xx // Avg Comment likes for post xx
        }
               ],
    "ok" : 1
}


标签: mongodb