Fortunately, MongoDB lets you get the sum of all field values in an aggregation group. And you can handle that with MongoTemplate in Spring Boot.
In this guide, I'll show you how to do just that.
If you'd rather just look at the source code, though, you can do so on GitHub.
Otherwise, read on.
The Business Requirements
Your boss Smithers walks into your office. He's chewing what appears to be a giant piece of gum.
"About that CRM app you're working on," he says while smacking his gum. "I need you to add a new service request. We need to add up all sales per contact."
He blows a bubble and pops it.
"You can do that, can't you?"
Before waiting for an answer, he walks out of your office blowing another bubble.
Your Starting Point
Fortunately, you've already got a lot to work with in your ContactService class. After all, you've already created plenty of aggregations.
By the way, if you need to start from the beginning on using MongoDB aggregation pipelines in Spring Boot with MongoTemplate, feel free to check out my first guide on the subject. Then come back here.
Now let's assume that the data in your contacts database looks like this:
[ {
"id" : "5fde12d60ab013769b67cf02",
"firstName" : "Chew",
"lastName" : "Bacca",
"email" : "chewie@xmail.com",
"source" : "WEBSITE_FORM",
"status" : "ACTIVE",
"statusChange" : 0,
"linesOfBusiness" : [ "ANGULAR" ],
"company" : "Working for Han",
"title" : "Wookie",
"authority" : false,
"salesOwner" : {
"id" : "5f78d8fbc1d3246ab4303f2b",
"firstName" : "Darth",
"lastName" : "Vader",
"email" : "darth@xmail.com",
"username" : "darth",
"phoneNumber" : "474-555-1212"
},
"sales" : [ {
"purchaseOrderNumber" : "09393",
"title" : "Angular UI Refactoring",
"value" : 100000,
"date" : 1609650000000,
"lineOfBusiness" : "ANGULAR"
}, {
"purchaseOrderNumber" : "09394",
"title" : "Revamp CRM UI",
"value" : 150000,
"date" : 1610254800000,
"lineOfBusiness" : "ANGULAR"
}, {
"purchaseOrderNumber" : "09395",
"title" : "Angular tutorial",
"value" : 20000,
"date" : 1610427600000,
"lineOfBusiness" : "ANGULAR"
} ]
}, {
"id" : "5fde1028792009283c603929",
"firstName" : "JarJar",
"lastName" : "Binks",
"email" : "jarjar@xmail.com",
"addresses" : [ {
"street1" : "1400 Plum Way",
"city" : "Onisius",
"state" : "NM",
"zip" : "80909",
"addressType" : "HOME"
} ],
"source" : "WALKIN",
"status" : "CONTACTED",
"statusChange" : 0,
"linesOfBusiness" : [ "FULL_STACK" ],
"company" : "None",
"title" : "Comic Relief",
"authority" : false,
"salesOwner" : {
"id" : "5f78d8fbc1d3246ab4303f2b",
"firstName" : "Darth",
"lastName" : "Vader",
"email" : "darth@xmail.com",
"username" : "darth",
"phoneNumber" : "474-555-1212"
},
"sales" : [ {
"purchaseOrderNumber" : "09396",
"title" : "Spring Boot API for In-House REST Service",
"value" : 310000,
"date" : 1609736400000
}, {
"purchaseOrderNumber" : "09396",
"title" : "Angular Optimization",
"value" : 200000,
"date" : 1610859600000,
"lineOfBusiness" : "ANGULAR"
}, {
"purchaseOrderNumber" : "09397",
"title" : "Full Stack Work",
"value" : 110000,
"date" : 1610946000000,
"lineOfBusiness" : "FULL_STACK"
} ]
}, {
"id" : "5fde117edd79e20e3ff6528c",
"firstName" : "Lando",
"lastName" : "Calrissian",
"email" : "lando@xmail.com",
"phones" : [ {
"phone" : "(555) 555-5555",
"phoneType" : "WORK",
"countryCode" : "us"
} ],
"source" : "INBOUND_SALES_CALL",
"status" : "CONTACTED",
"statusChange" : 0,
"linesOfBusiness" : [ "ANGULAR", "DEV_OPS" ],
"company" : "Cloud City",
"title" : "Friend",
"authority" : false,
"salesOwner" : {
"id" : "5f78d8fbc1d3246ab4303f2b",
"firstName" : "Darth",
"lastName" : "Vader",
"email" : "darth@xmail.com",
"username" : "darth",
"phoneNumber" : "474-555-1212"
},
"sales" : [ {
"purchaseOrderNumber" : "09398",
"title" : "Harness Training",
"value" : 150000,
"date" : 1610082000000,
"lineOfBusiness" : "DEV_OPS"
}, {
"purchaseOrderNumber" : "09399",
"title" : "Jenkins Training",
"value" : 150000,
"date" : 1610254800000,
"lineOfBusiness" : "DEV_OPS"
} ]
}, {
"id" : "5fde1ac084dad94dbb7f82ae",
"firstName" : "R2D2",
"lastName" : "Droid",
"email" : "r2d2@xmail.com",
"source" : "EMAIL",
"status" : "ACTIVE",
"statusChange" : 0,
"linesOfBusiness" : [ "JAVA_ENTERPRISE" ],
"company" : "For Luke",
"title" : "Droid",
"authority" : false,
"salesOwner" : {
"id" : "5f78d8fbc1d3246ab4303f2b",
"firstName" : "Darth",
"lastName" : "Vader",
"email" : "darth@xmail.com",
"username" : "darth",
"phoneNumber" : "474-555-1212"
}
}, {
"id" : "5fdd0af34e9d6806f369abf0",
"firstName" : "Boba",
"lastName" : "Fett",
"email" : "boba@xmail.com",
"phones" : [ {
"phone" : "(555) 555-5555",
"phoneType" : "HOME",
"countryCode" : "us"
} ],
"addresses" : [ {
"street1" : "1222 Galaxy Way",
"city" : "Alterion",
"state" : "AR",
"zip" : "22222",
"country" : "US",
"addressType" : "HOME"
} ],
"source" : "INBOUND_SALES_CALL",
"status" : "CONTACTED",
"statusChange" : 0,
"linesOfBusiness" : [ "DEV_OPS" ],
"company" : "Empire",
"title" : "Bounty Hunter",
"authority" : false,
"salesOwner" : {
"id" : "5f78d8fbc1d3246ab4303f2b",
"firstName" : "Darth",
"lastName" : "Vader",
"email" : "darth@xmail.com",
"username" : "darth",
"phoneNumber" : "474-555-1212"
},
"sales" : [ {
"purchaseOrderNumber" : "09400",
"title" : "Jenkins Training",
"value" : 150000,
"date" : 1610341200000,
"lineOfBusiness" : "DEV_OPS"
}, {
"purchaseOrderNumber" : "09401",
"title" : "Kubernetes Training",
"value" : 200000,
"date" : 1610686800000,
"lineOfBusiness" : "DEV_OPS"
} ]
}, {
"id" : "5fdd0cedaac5f75d62564ee7",
"firstName" : "Jabba",
"lastName" : "Hutt",
"email" : "jabba@xmail.com",
"source" : "EMAIL",
"status" : "NEW",
"statusChange" : 0,
"linesOfBusiness" : [ "ANGULAR" ],
"company" : "Sandz",
"title" : "Large",
"authority" : false,
"salesOwner" : {
"id" : "5f78d8fbc1d3246ab4303f2b",
"firstName" : "Darth",
"lastName" : "Vader",
"email" : "darth@xmail.com",
"username" : "darth",
"phoneNumber" : "474-555-1212"
},
"sales" : [ {
"purchaseOrderNumber" : "09403",
"title" : "Update UI to Match Wireframes",
"value" : 200000,
"date" : 1609822800000,
"lineOfBusiness" : "ANGULAR"
}, {
"purchaseOrderNumber" : "09403",
"title" : "Improve UI Responsiveness",
"value" : 150000,
"date" : 1610254800000,
"lineOfBusiness" : "ANGULAR"
} ]
}, {
"id" : "5fdd0e7c870ef4713e179384",
"firstName" : "Princess",
"lastName" : "Leia",
"email" : "leia@xmail.com",
"phones" : [ {
"phone" : "(555) 555-5555",
"phoneType" : "WORK",
"countryCode" : "us"
} ],
"source" : "WALKIN",
"status" : "INTERESTED",
"statusChange" : 0,
"linesOfBusiness" : [ "JAVA_ENTERPRISE", "ANGULAR" ],
"company" : "Republic",
"title" : "Princess",
"authority" : false,
"salesOwner" : {
"id" : "5f78d8fbc1d3246ab4303f2b",
"firstName" : "Darth",
"lastName" : "Vader",
"email" : "darth@xmail.com",
"username" : "darth",
"phoneNumber" : "474-555-1212"
},
"sales" : [ {
"purchaseOrderNumber" : "09404",
"title" : "Upgrade Spring Boot application to Java 11",
"value" : 150000,
"date" : 1610341200000,
"lineOfBusiness" : "JAVA_ENTERPRISE"
}, {
"purchaseOrderNumber" : "09406",
"title" : "Improve Exception Handling in Spring Boot application",
"value" : 250000,
"date" : 1610600400000,
"lineOfBusiness" : "JAVA_ENTERPRISE"
}, {
"purchaseOrderNumber" : "09407",
"title" : "Support Additional External REST services in Spring Boot",
"value" : 350000,
"date" : 1611118800000,
"lineOfBusiness" : "JAVA_ENTERPRISE"
}, {
"purchaseOrderNumber" : "09408",
"title" : "Enhance UI for Accounting App",
"value" : 150000,
"date" : 1611205200000,
"lineOfBusiness" : "ANGULAR"
} ]
}, {
"id" : "5fd5f37bde602d3bacef69db",
"firstName" : "Luke",
"lastName" : "Skywalker",
"email" : "luke@tat2.com",
"source" : "EMAIL",
"sourceDetails" : "He emailed me",
"status" : "NEW",
"statusChange" : 0,
"linesOfBusiness" : [ "ANGULAR" ],
"company" : "International Business",
"title" : "President",
"authority" : false,
"salesOwner" : {
"id" : "5f78d8fbc1d3246ab4303f2b",
"firstName" : "Darth",
"lastName" : "Vader",
"email" : "darth@xmail.com",
"username" : "darth",
"phoneNumber" : "474-555-1212"
}
}, {
"id" : "5fdd0f2aea599836ca3ddbf1",
"firstName" : "Han",
"lastName" : "Solo",
"email" : "han@xmail.com",
"addresses" : [ {
"street1" : "111 Millennium Way",
"city" : "Nessy",
"state" : "CO",
"addressType" : "HOME"
} ],
"source" : "EMAIL",
"status" : "ACTIVE",
"statusChange" : 0,
"title" : "Pirate",
"authority" : false,
"salesOwner" : {
"id" : "5f78d8fbc1d3246ab4303f2b",
"firstName" : "Darth",
"lastName" : "Vader",
"email" : "darth@xmail.com",
"username" : "darth",
"phoneNumber" : "474-555-1212"
}
}, {
"id" : "6005e87163660b7b2e6a16df",
"firstName" : "Governor",
"lastName" : "Tarkin",
"email" : "tarkin@xmail.com",
"phones" : [ {
"phone" : "(555) 555-5555",
"phoneType" : "CELL"
} ],
"addresses" : [ {
"city" : "Home City",
"state" : "MN",
"addressType" : "HOME"
} ],
"source" : "EMAIL",
"status" : "ACTIVE",
"linesOfBusiness" : [ "FULL_STACK" ],
"company" : "No Moon",
"title" : "Governor",
"authority" : true,
"salesOwner" : {
"id" : "6005e76bac127e0f5d9a6560",
"firstName" : "The",
"lastName" : "Emperor",
"email" : "theemperor@xmail.com",
"username" : "theemperor",
"phoneNumber" : "(555) 555-5555"
},
"sales" : [ {
"purchaseOrderNumber" : "09409",
"title" : "Refactor Spring Boot application",
"value" : 150000,
"date" : 1610341200000,
"lineOfBusiness" : "JAVA_ENTERPRISE"
}, {
"purchaseOrderNumber" : "09410",
"title" : "Full stack refactoring",
"value" : 250000,
"date" : 1610600400000,
"lineOfBusiness" : "FULL_STACK"
} ]
} ]As you can see, most of the contacts have a couple (or more) sales. Smithers needs you to put together an aggregation pipeline that shows the total sales for each contact.
For that, you'll use the sum operation.
Summing It Up
Start by adding a new method to that ContactService class. Make it look like this:
public List<SaleInfo> getTotalSalesPerContact() {
AggregationOperation match = Aggregation.match(Criteria.where("sales").exists(true));
AggregationOperation unwind = Aggregation.unwind("sales");
AggregationOperation fullName = Aggregation.project("_id", "sales").and("firstName").concat(" ", Aggregation.fields("lastName")).as("contactName");
AggregationOperation group = Aggregation.group("contactName").sum("sales.value").as("totalSales");
AggregationOperation project = Aggregation.project("totalSales").and("contactName").previousOperation();
Aggregation aggregation = Aggregation.newAggregation(match, unwind, fullName, group, project);
List<SaleInfo> saleInfo = mongoTemplate.aggregate(aggregation, mongoTemplate.getCollectionName(Contact.class), SaleInfo.class).getMappedResults();
return saleInfo;
}I'll step through each of those stages individually.
The first stage (match) eliminates contacts with no sales.
The second stage (unwind) unwinds the documents (contacts) around each sale. I've explained unwind in more detail elsewhere. You can check out that page if you're new to the concept.
The next stage (fullName) creates a full name for the contact by concatenating the contact's first name and last name with a space in between. That's a new field created on-the-fly.
The next stage (group) groups the documents by the contact's full name. Then, it sums up the sales value of all the documents in each group and saves it as a new field called "totalSales."
The final stage (project) associates the "totalSales" field with the contact's full name.
Note that the documents get translated to a Java SalesInfo object. That's an inner class that should look like this:
public static class SaleInfo {
private String contactName;
private Sale sale;
private Integer totalSales;
public String getContactName() {
return contactName;
}
public void setContactName(String contactName) {
this.contactName = contactName;
}
public Sale getSale() {
return sale;
}
public void setSale(Sale sale) {
this.sale = sale;
}
public Integer getTotalSales() {
return totalSales;
}
public void setTotalSales(Integer totalSales) {
this.totalSales = totalSales;
}
}
For the purposes of this guide, the sale property isn't used.
By the way, if you run that same pipeline on your MongoDB client, it will look like this:
db.contacts.aggregate(
[
{
"$match": {
"sales": {
"$exists": true
}
}
},
{
"$unwind": "$sales"
},
{
"$project": {
"_id": 1,
"sales": 1,
"contactName": {
"$concat": [
"$firstName",
" ",
"$lastName"
]
}
}
},
{
"$group": {
"_id": "$contactName",
"totalSales": {
"$sum": "$sales.value"
}
}
},
{
"$project": {
"totalSales": 1,
"_id": 0,
"contactName": "$_id"
}
}
]
)Trying It on for Size
Okay, so does that pipeline work? Let's find out.
Create some initialization code that looks like this:
@Component
public class ApplicationListenerInitialize implements ApplicationListener<ApplicationReadyEvent> {
@Autowired
private ContactService contactService;
public void onApplicationEvent(ApplicationReadyEvent event) {
List<SaleInfo> sales = contactService.getTotalSalesPerContact();
try {
ObjectMapper objectMapper = new ObjectMapper();
objectMapper.setSerializationInclusion(JsonInclude.Include.NON_NULL);
objectMapper.setSerializationInclusion(JsonInclude.Include.NON_EMPTY);
System.err.println(objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(sales));
} catch (Exception e) {
e.printStackTrace();
}
}
}Save that and restart your Spring Boot application. When everything settles, you should see this output in red:
[ {
"contactName" : "Governor Tarkin",
"totalSales" : 400000
}, {
"contactName" : "Lando Calrissian",
"totalSales" : 300000
}, {
"contactName" : "Jabba Hutt",
"totalSales" : 350000
}, {
"contactName" : "Chew Bacca",
"totalSales" : 270000
}, {
"contactName" : "Boba Fett",
"totalSales" : 350000
}, {
"contactName" : "JarJar Binks",
"totalSales" : 620000
}, {
"contactName" : "Princess Leia",
"totalSales" : 900000
} ]And if you do the math from up above, you'll see it all works out.
Wrapping It Up
Congratulations! You've successfully used MongoTemplate to handle a summation with the group aggregation operation.
Now, it's over to you. Why not try a more complicated operation with sum? Or add up different values.
And remember, you can always grab the source code on GitHub.
Have fun!
Photo by Katerina Holmes from Pexels