CODEWITHSUNDEEP
databasemongodb
Randy
Randy

Reputation: 33

MongoDB how to sort by multiple fields like this?

enter image description here

Sorry for my English. I want to sort like this, 'initial' status sort first，then sort by 'create_time'. Is this possible? I guess the 'aggregate' may be helpful, but I don't know how it can be used in this condition.

Upvotes: 3

Views: 13581

Answers (3)

s7vr
s7vr

Reputation: 75934

You can try below aggregation in 3.4 for custom sort.

Use $indexOfArray to locate the position of "initial", 0 when the status is found else for -1 for all other documents and $addFields to keep the output index in the extra field in the document followed by $sort sort on fields.

$project with exclusion to drop the sort field to get expected output.

When you sort all the initial status document shows at top followed by sort on create_time.

    db.col.aggregate([
        {
            "$addFields":{ 
                "statusValue":{
                    "$indexOfArray":[
                        ["initial"], "$status"
                    ]
                }
            }
        }, 
        {
            "$sort":{
                "statusValue":-1, "create_time":-1
            }
        },
        {
            "$project":{
                "statusValue":0
            }
        }
    ])

Upvotes: 5

Yogesh Kevre
Yogesh Kevre

Reputation: 29

aggregation can be used for advanced custom sort but in this scenario, you can use Sort() method. so let me just give you a small walk through over sort method in Mongo

"sort() method is used To sort documents in MongoDB, you need to use sort() method. The method accepts a document containing a list of fields along with their sorting order. To specify sorting order 1 and -1 are used. 1 is used for ascending order while -1 is used for descending order."

so in your case use sort() method:

Sorting by status than by create_time:

db.test.find().sort({status:-1, create_time:1})

Upvotes: 2

kevinadi
kevinadi

Reputation: 13775

Just to add to Veeram's answer, you can use the standard MongoDB sort() method (as per Ian Mercer's comment) as well as aggregation. For example:

> db.test.find()
{
  "_id": ObjectId("5a6eb96a2937803b14255182"),
  "status": "enabled",
  "create_time": "2018-01-16"
}
{
  "_id": ObjectId("5a6eb9722937803b14255183"),
  "status": "enabled",
  "create_time": "2018-01-10"
}
{
  "_id": ObjectId("5a6eb97b2937803b14255184"),
  "status": "disabled",
  "create_time": "2018-01-15"
}
{
  "_id": ObjectId("5a6eb9892937803b14255185"),
  "status": "initial",
  "create_time": "2018-01-08"
}

Sorting by status then by create_time:

> db.test.find().sort({status:1, create_time:1})
{
  "_id": ObjectId("5a6eb97b2937803b14255184"),
  "status": "disabled",
  "create_time": "2018-01-15"
}
{
  "_id": ObjectId("5a6eb9722937803b14255183"),
  "status": "enabled",
  "create_time": "2018-01-10"
}
{
  "_id": ObjectId("5a6eb96a2937803b14255182"),
  "status": "enabled",
  "create_time": "2018-01-16"
}
{
  "_id": ObjectId("5a6eb9892937803b14255185"),
  "status": "initial",
  "create_time": "2018-01-08"
}

Note that the sorting order on the example above are using string sort, so disabled will come before enabled as it is sorting in alphabetical order. Ditto with the create_time sorting order, which is based on string comparison sort.

For richer options with regards to sorting create_time, you need to convert the field into an ISODate() object. See Date() and Data Types in the mongo Shell

One advantage of using cursor sort like this is index use. Properly indexed, the query could be fast without the need to be sorted in memory, since MongoDB uses the index to perform the sort. See Create Indexes to Support Your Queries and Use Indexes to Sort Query Results.

If any of these concepts are new to you, I would suggest you peruse the MongoDB University for free courses about MongoDB.

Upvotes: 2

Related Questions