How to sort alphanumeric values in MongoDB

[Maddy M]

Hi All,

I have a requirement to sort alphanumeric values. Like - 

The system has to sort string values in "human readable" order.

For instance the following Job Names:

Job3

Job1

Job10

Job7

Should be sorted as:

Job1

Job3

Job7

Job10

NOT sorted as :

Job1

Job10

Job3

Job7

I have checked in mongo db but can't find out any reference. Is there any component available in mongo db ?  If any one have some ideas please share.

[Stone Fang]

actually cannot.

mongo sort string by ascii number.and this standard is followed by many conditions.

if sort by your way,it needs to scan all chars.and check if the value is number.

[Maddy M]

Hi Stone,

Thanks for your update.

[Conchi Bueno]

Hi Maddy,

As Stone pointed out, MongoDB is treating this field as a string. This means it treats everything within this field as a character, even numbers. 

Typically e.g. in programming languages, non-default sort order can be achieved by passing a custom sort function. However currently in MongoDB 3.4.4, there is no method to do so. However, there is an open ticket for this feature: SERVER-153. Also related to this: SERVER-14784. Please watch/upvote those tickets if you feel this feature is important for you.

There are different approaches that you could use in order to obtain the result you are expecting.

• First approach would be to divide the job field into two separate fields: the job string part and the job number part. 
  

{"job":"Job1"} -> {"job":"Job1","job_string":"Job","job_number":1}
{"job":"Job100"} -> {"job":"Job100","job_string":"Job","job_number":100}

You can then sort the documents with: 

db.collection.find(...).sort({job_string: 1, job_number: 1})

• Second approach would be zero-padding the string+number into a uniform length. For example:
  

{"job":"Job1"} -> {"job":"Job001"}
{"job":"Job100"} -> {"job":"Job100"}

If you still have issues, please post your MongoDB version and some example documents.

Regards,

Conchi

[Charlie Swanson]

Hi all,

This looks like a perfect use case for collation, which was introduced in version 3.4: https://docs.mongodb.com/manual/reference/collation/

> db.bar.find().sort({text: -1})
{ "_id" : 4, "text" : "joe4" }
{ "_id" : 2, "text" : "joe10" }
{ "_id" : 3, "text" : "joe1" }
> db.bar.find().sort({text: -1}).collation({locale: "en_US", numericOrdering: true})

{ "_id" : 2, "text" : "joe10" }
{ "_id" : 4, "text" : "joe4" }
{ "_id" : 3, "text" : "joe1" }

Best,

Charlie