CASE Statement in MongoDB

MongoDB does not have a direct CASE statement like traditional SQL databases such as MySQL or Teradata. However, you can achieve similar functionality using the aggregation framework and conditional operators like switch or cond.

Here's how you can emulate the CASE statement in MongoDB using the switch operator:

Suppose we have a collection named employees with documents containing information about employees, including their name and salary. We want to categorize employees into different salary ranges based on their salary.

db.employees.aggregate([

  {

    project: {

      name: 1,

      salary: 1,

      salary_range: {

        switch: {

          branches: [

            { case: { lt: ["salary", 50000] }, then: "Low" },

            { case: { and: [{ gte: ["salary", 50000] }, { lt: ["salary", 100000] }] }, then: "Medium" },

          ],

          default: "High"

        }

      }

    }

  }

])

In this aggregation pipeline:

- We use the project stage to include or exclude fields in the output documents.

- Inside the project stage, we define the salary_range field using the switch operator.

- The switch operator evaluates conditions in the specified order and returns the result of the first condition that evaluates to true. If none of the conditions match, it returns the default value.

- Each case specifies a condition (e.g., lt: ["salary", 50000]) and the corresponding result (e.g., "Low").

- The default specifies the default value to return if none of the conditions match (in this case, "High").

This aggregation pipeline categorizes employees into "Low", "Medium", or "High" salary ranges based on their salary.

You can adjust the conditions and results inside the switch operator as needed to fit your specific use case.