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?
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" }
}
}
])