COALESCE in MongoDB

In MongoDB, there isn't a direct equivalent to the COALESCE() function found in SQL databases. However, you can achieve similar functionality using the aggregation framework and the ifNull operator.

The ifNull operator evaluates an expression and returns the value of the first expression if it is not null, otherwise it returns the value of the second expression.

Here's how you can use $ifNull to achieve similar functionality to COALESCE():

Suppose you have a collection called employees with documents containing fields like name, salary, and bonus. Some employees might not have a bonus specified, and you want to replace those null values with a default value of 0.

{ "_id" : 1, "name" : "John", "salary" : 50000.00, "bonus" : 2000.00 }

{ "_id" : 2, "name" : "Alice", "salary" : 60000.00, "bonus" : null }

{ "_id" : 3, "name" : "Bob", "salary" : 55000.00, "bonus" : null }

You can use the aggregation framework to achieve the desired result:

mongodb

db.employees.aggregate([

  {

    project: {

      name: 1,

      salary: 1,

      bonus: {

        ifNull: ["$bonus", 0]

      }

    }

  }

])

This aggregation pipeline:

1. Uses the $project stage to reshape documents, including the name and salary fields as they are and using ifNull to replace null values in the bonus field with 0.

After running this aggregation pipeline, you would get the following output:

{ "_id" : 1, "name" : "John", "salary" : 50000.00, "bonus" : 2000.00 }

{ "_id" : 2, "name" : "Alice", "salary" : 60000.00, "bonus" : 0 }

{ "_id" : 3, "name" : "Bob", "salary" : 55000.00, "bonus" : 0 }

As you can see, for employees without a bonus specified (like Alice and Bob), the $ifNull operator replaces the null values with 0 as the default value. For John, who has a bonus specified, it retains the actual bonus value.