[SERVER-10689] Add a $switch expression Created: 06/Sep/13  Updated: 19/Jul/17  Resolved: 15/Apr/16

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: 3.3.5

Type: New Feature Priority: Major - P3
Reporter: Asya Kamsky Assignee: Benjamin Murphy
Resolution: Done Votes: 7
Labels: expression, usability
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
is depended on by CSHARP-1629 Add a $switch expression Closed
Documented
is documented by DOCS-9556 Docs for SERVER-10689: Add a $switch ... Closed
Related
is related to DRIVERS-297 Aggregation Framework Support for 3.4 Closed
Backwards Compatibility: Fully Compatible
Sprint: Query 13 (04/22/16)
Participants:

 Description   

Syntax

    {$project: {
        fieldName: {
            $switch: {
                branches: [
                    {
                        case: <expression>,
                        then: <expression>
                    },
                ],
                default: <optional, expression>
            }
        }
    }}

Examples

> db.coll.insert([
  {_id: 0, x: 0}
  {_id: 1, x: -1}
  {_id: 2, x: 2}
]);
> db.coll.aggregate([{
  $project: {
    z: {
      $switch: {
        branches: [
          {
            case: {$eq: ["$x", 0]},
            then: "Equal to zero."
          },
          {
            case: {$eq: [{$abs: "$x"}, 1]},
            then: "Equal to plus or minus one."
          },
        ],
        default: "Something else."
      }
    }
  }
}])
{_id: 0, z: "Equal to zero."}
{_id: 1, z: "Equal to plus or minus one."}
{_id: 2, z: "Something else."}

Notes

  • Allows $project expressions to be written without convoluted $conds.
  • If "case" does not return a boolean, the value will be coerced to a bool.
  • Since each case does not have to be mutually exclusive, an input will enter the first branch it matches.
    Errors
  • 'branches' is missing or not an array with at least one entry.
  • An entry in 'branches' does not contain 'case'
  • An entry in 'branches' does not contain 'then'
  • If 'default' is not specified, and an input doesn't match, an error will be thrown.

Old Description:
Currently if you want to do a multi-way switch statement equivalent in $project you have to do a very messy embedded sequence of {$cond:[]} statements (emulating an if-elif-elif-elif type sequence) which is very unreadable.

It would be nice to have an operator to do something like:

 'field: {$case:[ {condition1: val1}, {cond2: val2}, etc]}' 

where the first condition that matches sets the value of field to appropriate val.

Asya



 Comments   
Comment by stone [X] [ 19/May/17 ]

I used to think as you describe.
but get error that does not support $switch.but works on mongo shell(mongo 3.4)
nodejs-driver 2.2.26

BTW,will test again

Comment by Asya Kamsky [ 18/May/17 ]

cnStoneFang node driver doesn't need to have any special support - $switch is an expression and as long as you are running your aggregation against server version 3.4 or later, it will work.

Comment by stone [X] [ 18/May/17 ]

latest nodejs driver 2.26 still does not support $switch operation

Comment by Benjamin Murphy [ 15/Apr/16 ]

This ticket introduced the $switch expression to aggregation, which must be documented and supported by drivers.

Comment by Githook User [ 15/Apr/16 ]

Author:

{u'username': u'benjaminmurphy', u'name': u'Benjamin Murphy', u'email': u'benjamin_murphy@me.com'}

Message: SERVER-10689 Aggregation now supports the switch expression.
Branch: master
https://github.com/mongodb/mongo/commit/77aaa5419340185ad1744f0b25f8543c6add2abc

Comment by John Mullanaphy [ 10/Sep/13 ]

Going to also add in a vote. Had to make an adhoc $cond to do a fake join to do sorting by name. The solution(s): One table had ids, other table had dynamically changed names associated with the ids, so had to make the aggregate query in code with $cond matching ids to names and then sorting. That had ended up hitting a hash key nesting limit, so then the next solution was to make a:

{
    "field": {
        "$add": [
            {"$cond": [{"$eq":["$name_id","name1"]}, 1, 4]}
            {"$cond": [{"$eq":["$name_id","name1"]}, 2, 4]}
            {"$cond": [{"$eq":["$name_id","name1"]}, 3, 4]}
        ]
    }
}

Which also hits limits if you have large numbers of names for ids. Still, switch/case isn't the solution to our problem but while trying to find solutions we realized it would have been a nice alternative to our original nested conditionals. 2.6, will need to check out the $map/$let commands.

Comment by John Cole [ 06/Sep/13 ]

I'll second the need for this here. We were able to use $concat and a series of $cond to replicate a case statement but it's limited to returning strings.

Adding a $case as described would greatly improve the readability of agg queries that require strict grouping parameters, a common issue in reports.

Generated at Thu Feb 08 03:23:48 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.