Aug 24, 2015

Case statement in MongoDB query - aggregation

Using $cond (aggregation) we can perform case statement in MongoDB

SQL query:

SELECT fName,lName, (CASE WHEN totalOrder > 70 THEN 'Golden' ELSE 'Silver' END) AS UserType  FROM dbo.Users

In above query I am trying to apart user on the basis of UserType, on condition totalOrder. When totalOrder>70 then "Golden" otherwise "Silver".

Same scenario we can perform in MongoDB using $cond.


Syntax:

{ $cond: { if: <boolean-expression>, then: <true-case>, else: <false-case-> } }

Or:

{ $cond: [ <boolean-expression>, <true-case>, <false-case> ] }


Example:

Insert Document in Users Collection


{
    "fName" : "Dilip",
    "lName" : "Singh",
    "address" : "Noida",
    "totalOrder" : 150,
    "createdDate" : "2014/07/07"
}

{
    "fName" : "Vipul",
    "lName" : "Bhatt",
    "address" : "Delhi",
    "totalOrder" : 50,
    "createdDate" : "2013/07/07"
}

{
    "fName" : "Brijesh",
    "lName" : "Kumar",
    "address" : "Gorakhpur",
    "totalOrder" : 70,
    "createdDate" : "2012/07/07"
}


{
    "fName" : "Raj",
    "lName" : "Kumar",
    "address" : "Bokaro",
    "totalOrder" : 40,
    "createdDate" : "2010/07/07"
}




The following aggregation operation uses the $cond expression to set the UserType value to "Golden" when totalOrder is greater than 70 and UserType value to "Silver" in else condition.


db.Users.aggregate(
   [
      {
         $project:
           {
            fName :1,
             lName:1,
             UserType:
               {
                 $cond: { if: { $gte: [ "$totalOrder", 70 ] }, then: 'Golden', else: 'Silver' }
               }
           }
      }
   ]
)




Result:


/* 0 */
{
    "result" : [
        {
            "_id" : ObjectId("55dac0b31c949abf34d5daed"),
            "fName" : "Dilip",
            "lName" : "Singh",
            "UserType" : "Golden"
        },
        {
            "_id" : ObjectId("55dac0c21c949abf34d5daf1"),
            "fName" : "Vipul",
            "lName" : "Bhatt",
            "UserType" : "Silver"
        },
        {
            "_id" : ObjectId("55dac0d11c949abf34d5daf2"),
            "fName" : "Brijesh",
            "lName" : "Kumar",
            "UserType" : "Golden"
        },
        {
            "_id" : ObjectId("55dac0e31c949abf34d5daf3"),
            "fName" : "Raj",
            "lName" : "Kumar",
            "UserType" : "Silver"
        }
    ],
    "ok" : 1
}