MongoDB project condition - How to use $project with a condition

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:

idnamenumSoldpricequantitydepartment
1Chocolate Milk2501.9912Dairy
2Oat Milk21.992Dairy
3Chocolate Almonds1103.39112Nuts
4Salsa Sunflower Seeds43.2952Nuts
5Chocolate Bar5500.9989Candy
6Candy Corn100.8988Candy

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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.