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.