How should I calculate sum of field grouped by another one using Spring Data MongoDB

Question

I've a collection Users which looks something like this-

{ "post": "teacher",
  "salary": 4500
},
{ "post": "teacher",
  "salary": 9000
},
{ "post": "principal",
  "salary": 7000
},
{ "post": "teacher",
  "salary": 4500
}

I want to calculate the total salary of all the teachers together and the same way for principals. So I want something which looks like

"teachers_salary": 18000
"principals_salary": 7000

I want to use aggregation but I'm not getting the desired output. It'd be really helpful if anyone of you can help me get a solution to this.

Answer 1

Here is an example of aggregating using spring-data-mongodb.

Assuming a results model object of PostTotalSalary.class:

public class PostTotalSalary {
    private String post; // "teacher" or "principal"
    private Integer totalSalary;
}

We will create a GroupOperation to gather all documents with the same value with a key of "post"

GroupOperation groupByPostAndSumSalary = group("post")
  .sum("salary")
  .as("totalSalary");

We can now use spring-data-mongodb to create an aggregation and then map the results to your results model object (assuming a collection name of "posts"):

Aggregation aggregation = Aggregation.newAggregation(groupByPostAndSumSalary);
AggregationResults<PostTotalSalary> groupResults = mongoTemplate.aggregate(aggregation, "posts", PostTotalSalary.class);

If you then want a list, AggregationResults has the getMappedResults method to do this:

List<PostTotalSalary> mappedResults = groupResults.getMappedResults();

The end result will be

[
    {
        "post" : "teacher",
        "totalSalary" : "18000" // the total of all salaries for every "teacher" document
    },
    {
        "post" : "principal",
        "totalSalary" : "7000" // the total of all salaries for every "principal" document 
    }
]