Is there an elseif thing in MongoDB to $cond while aggregating

Question

So I need a custom field calculated in MongoDB as follows

if( field1 =="A")  ->customfield=10 else if(field1 =="B"  )->customfield=20 else (field1 =="C" ) ->customfield=15 

I'm using aggregation along with the $project statement. But the $cond operator doesn't allow elseif (subbranching the else) and merely allows two static branches if and else. Using a nested elseif causes

"exception: field inclusion is not allowed inside of $expressions"

Heres my query(which gives me the error)

db.items.aggregate([ { $project : {      name: 1,      customfield:      {          $cond: { if: { $eq: [ "$field1", "4" ] }, then: 30,                 else: {                     if:                      { $eq: ["$field1","8"]},                      then: 25, else: 10}}                }            }},{ $sort: { customfield: 1 }},{$limit:12}]); 

Is there a method or workaround to this. My apologies if this is a repeated question but I wasn't able to find a similar one.

Answer 1

With modern releases ( since MongoDB 3.4 ) you would use $switch, which is basically the counterpart to switch or case keywords in other language implementations:

db.items.aggregate([   { "$project": {     "name": 1,     "customfield": {       "$switch": {         "branches": [           { "case": { "$eq": [ "$field1", "4" ] }, "then": 30 },           { "case": { "$eq": [ "$field1", "8" ] }, "then": 25 }         ],         "default": 10       }     }   }},   { "$sort": { customfield: 1 }},   { "$limit":12 } ]) 

This avoids nesting the if..then..else conditions as can be done using $cond and shown below. But the below still shows as an example that this could always be done, even before the new operator of even the explicit if..then..else keywords since the original array notation always maintained that syntax.

Noting also that an array of conditions here is typically also a lot easier to construct programatically than creating a nested data structure for the statement as was needed with $cond.

---

The if..then..else keywords to the $cond operator are only a recent addition as of recent versions of MongoDB at the time of writing ( MongoDB 2.6 was the introduction of the keywords. The actual operator was available with release of the aggregation framework in MongoDB 2.2 ). The intention was for clarity but in this case it seems to has caused some confusion.

As an if..then.else operator $cond is indeed a ternary operator, just as would be implemented in many programming languages. This means as an "inline" conditional, rather than creating "blocks" of logic to the conditions, anything that does not meet the first condition belongs under else.

Therefore you "nest" the statements rather than follow blocks:

db.items.aggregate([   { "$project": {     "name": 1,     "customfield": {       "$cond": {          "if": { "$eq": [ "$field1", "4" ] },          "then": 30,         "else": {           "$cond": {             "if": { "$eq": ["$field1","8"]},              "then": 25,              "else": 10           }         }       }     }   }},   { "$sort": { customfield: 1 }},   { "$limit":12 } ]); 

Or even with the original array notation, which some might prefer if building the statement programatically:

db.items.aggregate([   { "$project": {     "name": 1,     "customfield": {       "$cond": [          { "$eq": [ "$field1", "4" ] },           30,          { "$cond": [            { "$eq": ["$field1","8"] },            25,             10          ]}       ]     }   }},   { "$sort": { customfield: 1 }},   { "$limit":12 } ]); 

Ternary means three conditions, no more no less. So all if..then..else logic must be nested.

Answer 2

MongoDB 3.4 has a new thing called $switch for this exact thing!

$switch: {    branches: [       { case: <expression>, then: <expression> },       { case: <expression>, then: <expression> },       ...    ],    default: <expression> } 

https://docs.mongodb.com/manual/reference/operator/aggregation/switch/