I have a feed collection with sample data like :
{
"_id" : ObjectId("55deb33dcb9be727e8356289"),
"userName" : "John",
"likeCount" : 2,
"commentCount" : 10,
}
,
{
"_id" : ObjectId("55deb33dcb3456e33434d456"),
"channelName" : "Mark",
"likeCount" : 5,
"commentCount" : 10,
},
{
"_id" : ObjectId("55acd3434324acd3e4567567"),
"userName" : "John",
"likeCount" : 12,
"commentCount" : 15,
}
I want to get all the records with group by "userName" and sum of "likeCount" + "commentCout". In Mysql we use :
select userName,sum(likeCount)+sum(commentCount) as "totalCount" from feed group by userName
How can I write the aggregation for above query?
I believe this can be achieved by adding an extra field that adds the likeCount and commentCount fields in the $project stage, then do the sum operation on that field for documents grouped by the userName key. Something like this in mongo query:
db.feed.aggregate([
{
"$project": {
"userName": 1,
"sumLikeAndCommentCounts": {
"$add": [ "$likeCount", "$commentCount" ]
}
}
},
{
"$group": {
"_id": "$userName",
"totalCount": {
"$sum": "$sumLikeAndCommentCounts"
}
}
}
])
OR
you just have one pipeline step $group where you can plug in the addition operation as an expression for the $sum:
db.feed.aggregate([
{
"$group": {
"_id": "$userName",
"totalCount": {
"$sum": {
"$add": [ "$likeCount", "$commentCount" ]
}
}
}
}
])
Both of the above operation pipelines will yield the result (for the given sample data):
{ "_id" : null, "totalCount" : 15 }
{ "_id" : "John", "totalCount" : 39 }
The Spring Data MongoDB aggregation equivalent is based on the first example, with an option of using the SpEL andExpression in the projection operation:
Aggregation agg = Aggregation.newAggregation(
project("id", "userName")
.and("likeCount").plus("commentCount").as("sumLikeAndCommentCounts"),
//.andExpression("likeCount + commentCount").as("sumLikeAndCommentCounts"), <-- or use expressions
group("userName").sum("sumLikeAndCommentCounts").as("totalCount")
);
AggregationResults<FeedsTotal> result =
mongos.aggregate(agg, this.getCollectionName(), FeedsTotal.class);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments