How to sort numeric strings as numbers in mongodb

This tutorial guides you how to compare and sort numeric strings as numbers in mongodb using Collation feature of Mongo.

Sort numeric strings as numbers in mongodb

You can use Collation to sort numeric strings as numbers in mongodb. Collation is a new feature in MongoDB version 3.4. They provide set of language-specific rules to compare strings that comply with the conventions of particular language, such as English or German.

A collation document has to the following fields.

{
   locale: <string>,
   caseLevel: <boolean>,
   caseFirst: <string>,
   strength: <int>,
   numericOrdering: <boolean>,
   alternate: <string>,
   maxVariable: <string>,
   backwards: <boolean>
}

Note, when you use collation, the locale field is mandatory and all other fields are optional. Let’s see how to use collation to compare and sort strings as numbers in mongodb with the following example.

As we discussed in previous tutorial use the following dump names.csv to create sample data in mongodb with collection name “names” for our exercise.

> show databases;

Response
--------
admin        0.000GB
config       0.000GB
local        0.000GB
mydb         0.000GB
sneppets     0.000GB
test         0.000GB

> use sneppets

Response
--------
switched to db sneppets

> show collections

Response
--------
items
names
posts

> db.names.find().sort({count:-1}).collation({locale:"en_US", numericOrdering:true}).limit(5);

Response
--------
{ "_id" : ObjectId("5e706e48fdcf8d3ea08566fe"), "name" : "Liam", "gender" : "M",
 "count" : "19837" }
{ "_id" : ObjectId("5e706e45fdcf8d3ea0852091"), "name" : "Emma", "gender" : "F",
 "count" : "18688" }
{ "_id" : ObjectId("5e706e48fdcf8d3ea08566ff"), "name" : "Noah", "gender" : "M",
 "count" : "18267" }
{ "_id" : ObjectId("5e706e45fdcf8d3ea0852092"), "name" : "Olivia", "gender" : "F
", "count" : "17921" }
{ "_id" : ObjectId("5e706e45fdcf8d3ea0852093"), "name" : "Ava", "gender" : "F",
"count" : "14924" }

You can see that the count field has string values and let’s say we wanted to sort records by count value which is string. To perform this sort you need specify the fields (count) to sort by and the value of 1 or -1 to specify ascending or descending sort.

Also, you need to use collation and specify locale field (mandatory), then specify numericOrdering field with value true to compare strings as numbers.

Note, numericOrdering is flag or a field of a collation document used to determine whether to compare numeric strings or as strings.

db.names.find().sort({count:-1}).collation({locale:"en_US", numericOrdering:true}).limit(5);

With the above solution now you are able to perform sort (descending) records by count as numbers instead of strings.

Also See

Sort numeric strings as numbers in mongodb

References