CODEWITHSUNDEEP
sortingmongodbconfiguration
Sadish Kumar
Sadish Kumar

Reputation: 531

custom Sorting order - configuration in mongoDB

I would like to know how to configure the DB for custom sorting sequence

Today, MongoDB sorts the String data in the following order: (used pattern to sort list of strings is -> [Symbols][0-9][A-Z][a-z] )

Example: Present Sorted Order will be like this.

  1. &%name
  2. 123AppName
  3. AppName
  4. SentinnelName
  5. appsname
  6. sampleName

But the way we want this sort is different. Pattern can be-> [Symbols][0-9][Aa-Zz]

Example: Expected Sorted Order should be like this

  1. &%name
  2. 123AppName
  3. AppName
  4. appsname
  5. SentinnelName
  6. sampleName

And my question is, do you have any idea of how to configure in mongoDB to customize regular expression pattern, to sort in our expected order. ?

I hope it is clear, if something is not clear please let me know to explain more in detail.

PS: I want to make this change at DB level, so that it holds the same for any strings when i do a sort over it.

Upvotes: 2

Views: 2715

Answers (3)

AD7six
AD7six

Reputation: 66169

Why don't you just add a key which by default sorts in the order you want. e.g.

> db.foo.insert({name: "&%name"})
> db.foo.insert({name: "123AppName"})
> db.foo.insert({name: "AppName"})
> db.foo.insert({name: "appsname"})
> db.foo.insert({name: "SentinnelName"})
> db.foo.insert({name: "sampleName"})

> db.foo.find().forEach(function(row) { db.foo.update({_id: row._id}, {$set: {sname: row.name.toLowerCase()}}); });

> db.foo.find()> db.foo.find({}, {name: 1}).sort({sname: 1})
{ "_id" : ObjectId("4faa32e2a1454519983b116e"), "name" : "&%name" }
{ "_id" : ObjectId("4faa32e8a1454519983b116f"), "name" : "123AppName" }
{ "_id" : ObjectId("4faa330ba1454519983b1170"), "name" : "AppName" }
{ "_id" : ObjectId("4faa3310a1454519983b1171"), "name" : "appsname" }
{ "_id" : ObjectId("4faa331aa1454519983b1173"), "name" : "sampleName" }
{ "_id" : ObjectId("4faa3315a1454519983b1172"), "name" : "SentinnelName" }

Upvotes: 1

Derick
Derick

Reputation: 36774

Right now, MongoDB does not implement collation yet. Implementing the Unicode collation standard is the best way for solving that, but as you can see while just glancing over that document, it's not going to be a simple task. Also, it would make sorting slower, and indexes larger. So for now, it's best to sort in your application, or add a field with your suggested sorting values—if you're crazy enough, you can implement the sorting algorithm from TR10 yourself.

Upvotes: 3

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230296

MongoDB doesn't use a regex to sort strings. It sorts them in lexicographical order. It just so happens that "%" < "0", "0" < "A" and "A" < "a" (based on their character codes).

However, "@" is lexicographically* between digits and capital letters. And it is sorted just like that.

% mongo
MongoDB shell version: 2.1.0
connecting to: test
> db.sort.insert({s: '777'})
> db.sort.insert({s: 'AAA'})
> db.sort.insert({s: '@@@'})

> db.sort.find().sort({s: 1})
{ "_id" : ObjectId("4faa33a07758e53f27a9896a"), "s" : "777" }
{ "_id" : ObjectId("4faa33b67758e53f27a9896c"), "s" : "@@@" }
{ "_id" : ObjectId("4faa33ad7758e53f27a9896b"), "s" : "AAA" }

So, from the top of my head, I don't know a way to alter MongoDB's behaviour. You should sort in application instead.

* Assuming ASCII or UTF-8 encoding

Upvotes: 1

Related Questions