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 https://lodash.com/docs#filter — dyouberg, Aug 25 '16 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, Aug 25 '16 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, Aug 25 '16 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, Aug 25 '16 at 14:52
Wasn't aware of what you were trying to do and to what scale. Ok... I'm thinking @chridam is on the right track then. You will have to use an aggregation pipeline with $match based on user input, and implement $sort and $limit at the necessary stages to shape your results. When you are using the aggregation framework - you aren't actually adding extra fields to the database. Just temporary variables to structure your query results. — dyouberg, Aug 25 '16 at 14:53
Yup, you'd need to shape your results with the `$project` pipeline where you can add or reshape your keys. As @dyouberg pointed out, the `$match` pipeline is just similar to the `find()` query, so is the `$sort` for `sort()` function on the cursor, `$limit` for `limit()` and `$skip` for `skip()`. — chridam, Aug 25 '16 at 14:59
@YanickRochon I think you misunderstood what chridam meant by the aggregation pipeline "creating an extra field". The $project stage of the pipeline doesn't change the actual database record, it adds a virtual field just in the pipeline processing. — Vince Bowdren, Aug 25 '16 at 15:32
@VinceBowdren I perfectly understood what people are saying, actually. the `$project` pipeline operator specifies which fields to return, allowing to add and compute extra fields as well. The point is that, `$project` cannot be used merely to add extra fields, but *all* other fields must be specified as well. — Yanick Rochon, Aug 25 '16 at 15:55
@mido Please look at [this](https://stackoverflow.com/q/47944695/2683814) 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, Dec 27 '17 at 15:10
@Veeram Yeah, I am aware of that, but hope there is a better solution than having to resort to aggregate when all you want is custom sort with find. Also `{$project:{"statusValue":0}}` would return `_ids` right? or all the other fields? — mido, Dec 27 '17 at 15:21
Okay. I don't think there is a solution using regular query language. `{$project:{"statusValue":0}}` will return all other fields including id field. — s7vr, Dec 27 '17 at 15:28

score 14 · Accepted Answer · edited Jan 03 '18 at 12:00

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" }

you can use [$addFields](https://docs.mongodb.com/manual/reference/operator/aggregation/addFields/) instead of project if you do not want to add the existing fields all over again. — mido, Dec 27 '17 at 15:20
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: http://www.kamsky.org/stupid-tricks-with-mongodb/using-34-aggregation-to-return-documents-in-same-order-as-in-expression — Asya Kamsky, Dec 29 '17 at 20:23
@AsyaKamsky you should add that as an answer. I am sure people can miss your comment, and it's a pretty good solution. — Yanick Rochon, Jan 03 '18 at 14:11

MongoDB sort with a custom expression or function

1 Answers1

Linked

Related