Skip to main content

Posts

Showing posts from August, 2015

Case statement in MongoDB query - aggregation

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

SQL query:
SELECTfName,lName,(CASEWHENtotalOrder> 70 THEN'Golden'ELSE'Silver'END)ASUserTypeFROMdbo.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", …

Like in MongoDB Query

In SQL, 'LIKE' query look like as below
SELECT*FROM[dbo.][USERS]WHEREFNAMELIKE'%DILIP%'
In MongoDB it's look like as below(query in MongoDB console)
db.USERS.find({"FNAME":/DILIP/})
Same as
db.USERS.find({"FNAME":/^DILIP/})   // FNAMELIKE'DILIP%'
db.USERS.find({"FNAME":/DILIP$/})   // FNAMELIKE'%DILIP'

$regex in MongoDB