21

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)

Yanick Rochon
  • 51,409
  • 25
  • 133
  • 214
  • 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
  • 1
    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
  • 1
    @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
  • 1
    @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

2 Answers2

18

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" }
Dhaval Asodariya
  • 558
  • 5
  • 19
user641887
  • 1,506
  • 3
  • 32
  • 50
  • 1
    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
  • 8
    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
  • 2
    @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
2

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.

Zain Waseem
  • 21
  • 3