Sort Result | Number Stored as String | Mongo DB Query.

Let discuss how we can sort a mongo DB document, based on any particular key. On many occasions we have to show the sorted data, to make it understandable at the client level.

Let us start from the very beginning, most of us who are already reached up to sorting must be aware of these few basic steps. But for others let me discuss in brief.

1. Creating a Database named as Employee, use the following command

> use Employee
switched to db Employee

2. Now if you will execute the command to check the available databases, you won't see any database with the name Employee, but don't worry the database is created in memory.

> show dbs
admin 0.000GB
local 0.000GB

3. Now let us add a couple of documents into the Employee database and Collection named as mycollection.

> db.mycollection.insert({"name" : "Apoorv","age":"11"})
WriteResult({ "nInserted" : 1 })
> db.mycollection.insert({"name" : "Rahul","age":"11"})
WriteResult({ "nInserted" : 1 })
> db.mycollection.insert({"name" : "Bobby","age":"2"})
WriteResult({ "nInserted" : 1 })
> db.mycollection.insert({"name" : "Leo","age":"7"})
WriteResult({ "nInserted" : 1 })

4. Now if we check the command to check the available databases.

> show dbs
Employee 0.000GB
admin  0.000GB
local  0.000GB

5. To check the collections available. Use the following command.

> show collections
mycollection

6. To check the documents available. Now you can verify there are the same which we added earlier.

> db.mycollection.find()
{ "_id" : ObjectId("60b4f23240e544166e0c773a"), "name" : "Apoorv", "age" : "11" }
{ "_id" : ObjectId("60b4f23a40e544166e0c773b"), "name" : "Rahul", "age" : "11" }
{ "_id" : ObjectId("60b4f25240e544166e0c773c"), "name" : "Bobby", "age" : "2" }
{ "_id" : ObjectId("60b4f26240e544166e0c773d"), "name" : "Leo", "age" : "7" }

7. Now it's time to sort, we need the query for sorting the data based on the name. Here -1 and +1 depict sorting in descending or ascending order.

Descending

> db.mycollection.find().sort({name:-1})
{ "_id" : ObjectId("60b4f23a40e544166e0c773b"), "name" : "Rahul", "age" : "11" }
{ "_id" : ObjectId("60b4f26240e544166e0c773d"), "name" : "Leo", "age" : "7" }
{ "_id" : ObjectId("60b4f25240e544166e0c773c"), "name" : "Bobby", "age" : "2" }
{ "_id" : ObjectId("60b4f23240e544166e0c773a"), "name" : "Apoorv", "age" : "11" }

Ascending

> db.mycollection.find().sort({name:1})

{ "_id" : ObjectId("60b4f23240e544166e0c773a"), "name" : "Apoorv", "age" : "11" }
{ "_id" : ObjectId("60b4f25240e544166e0c773c"), "name" : "Bobby", "age" : "2" }
{ "_id" : ObjectId("60b4f26240e544166e0c773d"), "name" : "Leo", "age" : "7" }
{ "_id" : ObjectId("60b4f23a40e544166e0c773b"), "name" : "Rahul", "age" : "11" } 

8. Now if we try to sort on the key, age. We will get some weird responses since we are expecting it to work like numbers, but since it is a string, this will behave differently.

Descending

> db.mycollection.find().sort({age:-1})
{ "_id" : ObjectId("60b4f26240e544166e0c773d"), "name" : "Leo", "age" : "7" }
{ "_id" : ObjectId("60b4f25240e544166e0c773c"), "name" : "Bobby", "age" : "2" }
{ "_id" : ObjectId("60b4f23240e544166e0c773a"), "name" : "Apoorv", "age" : "11" }
{ "_id" : ObjectId("60b4f23a40e544166e0c773b"), "name" : "Rahul", "age" : "11" }

Ascending

> db.mycollection.find().sort({age:1})
{ "_id" : ObjectId("60b4f23240e544166e0c773a"), "name" : "Apoorv", "age" : "11" }
{ "_id" : ObjectId("60b4f23a40e544166e0c773b"), "name" : "Rahul", "age" : "11" }
{ "_id" : ObjectId("60b4f25240e544166e0c773c"), "name" : "Bobby", "age" : "2" }
{ "_id" : ObjectId("60b4f26240e544166e0c773d"), "name" : "Leo", "age" : "7" }

9. Now if you want to cast a string into numbers and want to sort it as a number. You need to use collation.

Ascending

> db.mycollection.find().sort({age:1}).collation({locale:"en_US", numericOrdering:true})
{ "_id" : ObjectId("60b4f25240e544166e0c773c"), "name" : "Bobby", "age" : "2" }
{ "_id" : ObjectId("60b4f26240e544166e0c773d"), "name" : "Leo", "age" : "7" }
{ "_id" : ObjectId("60b4f23240e544166e0c773a"), "name" : "Apoorv", "age" : "11" }
{ "_id" : ObjectId("60b4f23a40e544166e0c773b"), "name" : "Rahul", "age" : "11" }

Descending

> db.mycollection.find().sort({age:-1}).collation({locale:"en_US", numericOrdering:true})
{ "_id" : ObjectId("60b4f23240e544166e0c773a"), "name" : "Apoorv", "age" : "11" }
{ "_id" : ObjectId("60b4f23a40e544166e0c773b"), "name" : "Rahul", "age" : "11" }
{ "_id" : ObjectId("60b4f26240e544166e0c773d"), "name" : "Leo", "age" : "7" }
{ "_id" : ObjectId("60b4f25240e544166e0c773c"), "name" : "Bobby", "age" : "2" }

That's how you can sort the documents based on the key that is number but are stored in the form of string.

---

Newsletter

Subscribe to my Newsletter, Such informative content will reach directly to your mailbox. Click to reach