MongoDB project condition - How to use $project with a condition
Introduction
This is a fairly specific problem we are dealing with in this article but it comes up quite frequently in developer forums so we wanted to go through the problem and offer some insight as well as a demonstration.
Our Approach
It’s harder to explain the problem so we’ll introduce some data and how we’d like to aggregate that data as a starting point.
The Data
Assume we are managing a database grocerydb
for a small grocery store. Inside that database there is a collection called products
with this data:
id | name | numSold | price | quantity | department |
---|---|---|---|---|---|
1 | Chocolate Milk | 250 | 1.99 | 12 | Dairy |
2 | Oat Milk | 2 | 1.99 | 2 | Dairy |
3 | Chocolate Almonds | 110 | 3.39 | 112 | Nuts |
4 | Salsa Sunflower Seeds | 4 | 3.29 | 52 | Nuts |
5 | Chocolate Bar | 550 | 0.99 | 89 | Candy |
6 | Candy Corn | 10 | 0.89 | 88 | Candy |
There are two items in each department and as you can see the Chocolate Milk, Chocolate Almonds, and Chocolate Bar have many more numSold
than the other item in the department.
Now let’s take a look at how this data might look in a non relational database:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | [{ id: 1, name: "Chocolate Milk", numSold: 250, price: 1.99, quantity: 12, department: "Dairy" }, { id: 2, name: "Oat Milk", numSold: 2, price: 1.99, quantity: 2, department: "Dairy" }, { id: 3, name: "Chocolate Almonds", numSold: 110, price: 3.39, quantity: 112, department: "Nuts" }, { id: 4, name: "Salsa Sunflower Seeds", numSold: 4, price: 3.29, quantity: 52, department: "Nuts" }, { id: 5, name: "Chocolate Bar", numSold: 550, price: 0.99, quantity: 89, department: "Candy" }, { id: 6, name: "Candy Corn", numSold: 10, price: 0.89, quantity: 88, department: "Candy" }] |
The Problem
Now, the problem is that we want to get a report of all the hottest items in each department. An item is considered “hot” if it has been purchased more than 100 times ( numPurchases > 100). We want a new field “hot” that if the product is “hot” has the value of numSold, but if it is not “hot”, then it has the value 0. How do we do this in a query?
Use the conditional operator $cond
We want you to take a look at the final query so you can start about it before we explain:
1 2 3 4 5 6 7 | db.products.aggregate( { $project: { _id: true, name: true, department: '$department', hot: {$cond: [{$gt: ['$numSold', 100]}, '$numSold', 0 ]} }}).pretty(); |
The $cond
operator acts as a conditional statement. If you’re familiar with ternary operators or if-else statements in programming this is basically the same concept.
Let’s look at the definition then we’ll dissect how we used the $cond
operator:
1 | { $cond: [ <boolean-expression>, <true-case>, <false-case> ] } |
In the <boolean-expression>
we provide an expression that evaluates to true or false. If it is true it uses the value in the <true-case>
. If it is false it uses the value in the <false-case>
.
In our case we needed to check if the numSold was greater than 100. So our <boolean-expression>
used the $gt
greater than operator {$gt: ['$numSold', 100]}
which evaluates to true if numSold is > 100. Then in the next parameter we provided the value for the hot field if numSold > 100 (<true-case>
). We assign the value of numSold if it’s a hot item. If the product is false though, we use the <false-case>
to specify a value of 0. If you run the query we see a result and see that we have computed the new “hot” field for the products.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | { "_id" : ObjectId("5d1cce7505211c122797df63"), "name" : "Chocolate Milk", "department" : "Dairy", "hot" : 250 } { "_id" : ObjectId("5d1cce7505211c122797df64"), "name" : "Oat Milk", "department" : "Dairy", "hot" : 0 } { "_id" : ObjectId("5d1cce7505211c122797df65"), "name" : "Chocolate Almonds", "department" : "Nuts", "hot" : 110 } { "_id" : ObjectId("5d1cce7505211c122797df66"), "name" : "Salsa Sunflower Seeds", "department" : "Nuts", "hot" : 0 } { "_id" : ObjectId("5d1cce7505211c122797df67"), "name" : "Chocolate Bar", "department" : "Candy", "hot" : 550 } { "_id" : ObjectId("5d1cce7505211c122797df68"), "name" : "Candy Corn", "department" : "Candy", "hot" : 0 } |
It worked!
Alternate Syntax
If the syntax for the $cond
operator is confusing to you, there is an alternate syntax that is a little more explicit. Let’s check out how we’d perform the same operation as before with the new syntax:
1 2 3 4 5 6 7 8 9 10 | db.products.aggregate( { $project: { _id: true, name: true, department: '$department', hot: { $cond: { if: { $gt: ['$numSold', 100] }, then: '$numSold', else: 0 } } }}).pretty(); |
Which we can verify returns the same result:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | { "_id" : ObjectId("5d1cce7505211c122797df63"), "name" : "Chocolate Milk", "department" : "Dairy", "hot" : 250 } { "_id" : ObjectId("5d1cce7505211c122797df64"), "name" : "Oat Milk", "department" : "Dairy", "hot" : 0 } { "_id" : ObjectId("5d1cce7505211c122797df65"), "name" : "Chocolate Almonds", "department" : "Nuts", "hot" : 110 } { "_id" : ObjectId("5d1cce7505211c122797df66"), "name" : "Salsa Sunflower Seeds", "department" : "Nuts", "hot" : 0 } { "_id" : ObjectId("5d1cce7505211c122797df67"), "name" : "Chocolate Bar", "department" : "Candy", "hot" : 550 } { "_id" : ObjectId("5d1cce7505211c122797df68"), "name" : "Candy Corn", "department" : "Candy", "hot" : 0 } |
Similar to Javascript Ternary Operator
If you’re familiar with the ternary operator in javascript the $cond is similar to that:
1 2 3 | function getPrice(isMember) { return (isMember ? "$2.00" : "$10.00"); } |
Conclusion
In this article we showed you how to use the $cond
expression in a projection with MongoDB. This has many use cases and it’s easy to understand once you’ve grasped the concept. We showed you how it’s more explicit syntax and also a similar construct in Javascript. Thanks for reading and if you need any help with your database please don’t hesitate to reach out to us at Object Rocket.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started