CODEWITHSUNDEEP
mongodb-query
Rammohan Reddy
Rammohan Reddy

Reputation: 51

Case-Insensitive Sorting with MongoDB Query

I am using "MongoDB-3.6.17" and "mongo-java-driver-3.6.4" in my application. The following are the documents in a MongoDB collection:

{ "_id" : ObjectId("5323850fa89de4a9f691dacf"), "name" : "PQR" }
{ "_id" : ObjectId("53238511a89de4a9f691dad0"), "name" : "abc" }
{ "_id" : ObjectId("53238515a89de4a9f691dad1"), "name" : "ABC" }
{ "_id" : ObjectId("53238522a89de4a9f691dad2"), "name" : "xyz" }
{ "_id" : ObjectId("5323852ea89de4a9f691dad3"), "name" : "XYZ" }
{ "_id" : ObjectId("5323855ea89de4a9f691dad4"), "name" : "pqr" }

Then I have queried with sorting by name:

db.collection.find().sort({name:1});

And the result is as follows:

{ "_id" : ObjectId("53238515a89de4a9f691dad1"), "name" : "ABC" }
{ "_id" : ObjectId("5323850fa89de4a9f691dacf"), "name" : "PQR" }
{ "_id" : ObjectId("5323852ea89de4a9f691dad3"), "name" : "XYZ" }
{ "_id" : ObjectId("53238511a89de4a9f691dad0"), "name" : "abc" }
{ "_id" : ObjectId("5323855ea89de4a9f691dad4"), "name" : "pqr" }
{ "_id" : ObjectId("53238522a89de4a9f691dad2"), "name" : "xyz" }

By default MongoDB is supporting Case-Sensitive sorting. How do we get the Case-Insensitive sort results with MongoDB query.

Expected Results:

{ "_id" : ObjectId("53238515a89de4a9f691dad1"), "name" : "ABC" }
{ "_id" : ObjectId("53238511a89de4a9f691dad0"), "name" : "abc" }
{ "_id" : ObjectId("5323850fa89de4a9f691dacf"), "name" : "PQR" }
{ "_id" : ObjectId("5323855ea89de4a9f691dad4"), "name" : "pqr" }
{ "_id" : ObjectId("5323852ea89de4a9f691dad3"), "name" : "XYZ" }
{ "_id" : ObjectId("53238522a89de4a9f691dad2"), "name" : "xyz" }

Kindly help me some one on case-insensitive sorting in MongoDB.

Thank you.

Regards, Ram

Upvotes: 2

Views: 620

Answers (1)

Sairaj Sawant
Sairaj Sawant

Reputation: 1842

Using MongoDB Aggregation Framework

db.collection.aggregate([
    { "$project": {
       "name": 1, 
       "lowerName": { "$toLower": "$name" }
    }},
    { "$sort": { "lowerName": 1 } }
])

or you can also use $addFields

db.collection.aggregate([
    { "$addFields": {
       "lowerName": { "$toLower": "$name" }
    }},
    { "$sort": { "lowerName": 1 } }
])

Reference - http://codingpajamas.github.io/2015/05/14/sorting-mongodb-case-insensitive

Upvotes: 2

Related Questions