MongoDB sort with a custom expression or function

Question

One of my collections is a list of operations (or tasks) with some status. For example, a list of documents can look like this

{
  _id: '57befe57fc956d2e3252890c',
  task: 'Go buy milk',
  status: 'pending'
},
{
  _id: '57befe5efc956d2e3252890d',
  task: 'Cook dinner',
  status: 'complete'
},
{
  _id: '57befe5efc956d2e3252890e',
  task: 'Play games',
  status: 'new'
}

I want to sort this list based on a their status, where new > pending > complete.

How can I do that with MongoDB without having to create an extra field? I am asking as in my case the sorting order may be pre-configured (i.e. users could have their preferences to pending > new > complete for example)

Is there a reason as to why you cannot handle the sorting on the client side? Best bet would be to return all documents and then use filter to create the subgroups based on the user preference order lodash.com/docs#filter — dyouberg
– dyouberg, Commented Aug 25, 2016 at 14:46
You could do this with an aggregation pipeline that has two pipelines, $project and $sort. The $project will create an extra field, say score that is populated based on a preconfigured order with the $cond operator, so for instance for the above score for status "new" will be 3, for pending 2 etc, then the $sort pipeline will sort on the score field. — chridam
– chridam, Commented Aug 25, 2016 at 14:49
@dyouberg I am using pagination, and the sorting needs to be done before $limit, on the server side, otherwise the pagination is all messed up. Why would you suggest downloading all items to the client? That's a bad use of bandwidth and processing. — Yanick Rochon
– Yanick Rochon, Commented Aug 25, 2016 at 14:51
@chridam so, I cannot just add extra fields, I need to specify them all, adding whatever fields I need as well? — Yanick Rochon
– Yanick Rochon, Commented Aug 25, 2016 at 14:52
@mido Please look at this solution. $addFields to add new sort field while retaining all the existing fields. This looks exactly what you need. You can add {$project:{"statusValue":0}} to drop the field as the last stage. — s7vr
– s7vr, Commented Dec 27, 2017 at 15:10

Dhaval Asodariya · Accepted Answer · 2018-01-03 12:00:12Z

22

Based on what @chirdam said here is what the implementation looks like. Also the new sorted field is not present in the result as requested.

DataSet :

{
  _id: '57befe57fc956d2e3252890c',
  task: 'Go buy milk',
  status: 'pending'
},
{
  _id: '57befe5efc956d2e3252890d',
  task: 'Cook dinner',
  status: 'complete'
},
{
  _id: '57befe5efc956d2e3252890e',
  task: 'Play games',
  status: 'new'
}

Query :

db.task.aggregate([
    { "$project" : {
        "_id" : 1,
        "task" : 1,
        "status" : 1,
        "order" : {
            "$cond" : {
                if : { "$eq" : ["$status", "new"] }, then : 1,
                else  : { "$cond" : {
                    "if" : { "$eq" : ["$status", "pending"] }, then : 2, 
                    else  : 3
                    }
                }
            }
        }
    } }, 
    {"$sort" : {"order" : 1} },
    { "$project" : { "_id" : 1, "task" : 1, "status" : 1 } }
])

Output:

{ "_id" : "57befe5efc956d2e3252890e", "task" : "Play games", "status" : "new" }
{ "_id" : "57befe57fc956d2e3252890c", "task" : "Go buy milk", "status" : "pending" }
{ "_id" : "57befe5efc956d2e3252890d", "task" : "Cook dinner", "status" : "complete" }

edited Jan 3, 2018 at 12:00

Dhaval Asodariya

5585 silver badges19 bronze badges

answered Aug 25, 2016 at 17:59

user641887

1,5863 gold badges36 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mido Over a year ago

you can use $addFields instead of project if you do not want to add the existing fields all over again.

Asya Kamsky Over a year ago

Rather than multiple if-then-else sequences you can just put the desired order into an array and use "$indexOfArray" expression to generate the sort field. There's an example here: kamsky.org/stupid-tricks-with-mongodb/…

Yanick Rochon Over a year ago

@AsyaKamsky you should add that as an answer. I am sure people can miss your comment, and it's a pretty good solution.

Zain Waseem · Accepted Answer · 2023-03-05 16:59:13Z

Example of our collection:

{ "_id" : ObjectId("580e51fc87a0572ee623854f"), "name" : "Asya" }
{ "_id" : ObjectId("580e520087a0572ee6238550"), "name" : "Charlie" }
{ "_id" : ObjectId("580e520587a0572ee6238551"), "name" : "Tess" }
{ "_id" : ObjectId("580e520887a0572ee6238552"), "name" : "David" }
{ "_id" : ObjectId("580e520c87a0572ee6238553"), "name" : "Kyle" }
{ "_id" : ObjectId("580e521287a0572ee6238554"), "name" : "Aly" }

The query we want to run is one that will return all documents where name is one of "David", "Charlie" or "Tess" and we want them in that exact order.

> db.people.find({"name":{"$in": ["David", "Charlie", "Tess"]}}).sort({ ??? })

Let's define a variable called "order" so we don't have to keep typing the names in the array:

> order = [ "David", "Charlie", "Tess" ]

Here's how we can do this with aggregation framework:

    m = { "$match" : { "name" : { "$in" : order } } };
    a = { "$addFields" : { "__order" : { "$indexOfArray" : [ order, "$name" ] } } };
    s = { "$sort" : { "__order" : 1 } };
    db.people.aggregate( [ m, a, s ] );

Our result:

{ "_id" : ObjectId("580e520887a0572ee6238552"), "name" : "David", "__order" : 0 }
{ "_id" : ObjectId("580e520087a0572ee6238550"), "name" : "Charlie", "__order" : 1 }
{ "_id" : ObjectId("580e520587a0572ee6238551"), "name" : "Tess", "__order" : 2 }

The "$addFields" stage is new in 3.4 and it allows you to "$project" new fields to existing documents without knowing all the other existing fields. The new "$indexOfArray" expression returns position of particular element in a given array.

The result of this aggregation will be documents that match your condition, in order specified in the input array "order", and the documents will include all original fields, plus an additional field called "__order". If we want to remove this field, 3.4 allows "$project" stage with just exclusion specification, so we would just add { "$project": {"__order":0}} at the end of our pipeline.

Collectives™ on Stack Overflow

MongoDB sort with a custom expression or function

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related