CODEWITHSUNDEEP
databasemongodbmongodb-queryaggregation-frameworkaggregate
Pengibaby
Pengibaby

Reputation: 373

MongoDB: List the usernames and the amount of replies they received

I have a collection set up in mongoDB with sample comments made by users from a made up social media platform, in this form:

{
     "_id" : ObjectId("5aa58936c4214f42f4c666b8"),
     "id" : "85",
     "user_name": "Alex4Ever",
     "text" : "This is a comment",
     "in_reply_to_user_name": "SamLad"
},
{
     "_id" : ObjectId("5aa58935c4214f42f4c66608"),
     "id" : "86",
     "user_name": "SamLad",
     "text" : "I am inevitable",
     "in_reply_to_user_name": null
},
{
     "_id" : ObjectId("5aa588e4c4214f42f4c63caa"),
     "id" : "87",
     "user_name": "HewwoKitty",
     "text" : "What is grief, if not love persevering?",
     "in_reply_to_user_name": "Alex4Ever"
} //There are more, but for testing purposes, I only use these 3 for now.

I have to come up with a query in MongoDB to list all the users in the file along with the amount of replies they received. So in the above sample bit of file, the output should be like:

"_id": "Alex4Ever", "replyCount" : 1,  //HewwoKitty replied to Alex4Ever
"_id": "SamLad", "replyCount" : 1,     //Alex4Ever replied to SamLad
"_id": "HewwoKitty", "replyCount" : 0, //No one replied to HewwoKitty

My attempt at doing this:

db.comments.aggregate([
                      {$match:{"in_reply_to_user_name":{"$exists":true, "$ne":null}}},
                      {$group: { _id: "$in_reply_to_user_name", replyCount:{$sum: 1}}}, 
                      {$sort:{replyCount: -1}}
                     ]).pretty()

However, I only get the non-zero values, i.e. I do not get HewwoKitty with a replyCount of 0. Is there any way to print all 3 lines, including the lines with 0 replies?

Upvotes: 1

Views: 62

Answers (1)

Tushar Gupta - curioustushar
Tushar Gupta - curioustushar

Reputation: 57105

Demo - https://mongoplayground.net/p/JA9YasEYuVV

Use $lookup and create self join to get all replies for a user and use $size to get the count of replies, after that $group them on user_name.

Extract the replyCount, take $first value from the group

db.collection.aggregate([
  {
    "$match": {
      "in_reply_to_user_name": { "$exists": true }
    }
  },
  {
    "$lookup": {
      "from": "collection",
      "localField": "user_name",
      "foreignField": "in_reply_to_user_name",
      "as": "replies"
    }
  },
  {
    "$project": {
      "user_name": 1,
      "replyCount": { "$size": "$replies" }
    }
  },
  {
    "$group": {
      "_id": "$user_name",
      "replyCount": {  "$first": "$replyCount" }
    }
  }
])

Upvotes: 3

Related Questions