Sort document alphabetically (aka natural sort order, sorting for humans) in MongoDB

Question

I would like to find out how to do this with MongoDB

I have documents with names as "file1", "file2", "file22", "file11" (name can be anything, there is no particular pattern) I ran the query to get all documents sorted by name and the result is not as expected.

> db.mydata.find().sort({"name":1});                                                                                                                          
{ "_id" : ObjectId("571e5a787e88d30b20b7857c"), "name" : "file1" }                                                                                            
{ "_id" : ObjectId("571e5a8c7e88d30b20b7857d"), "name" : "file11" }                                                                                           
{ "_id" : ObjectId("571e5a977e88d30b20b7857f"), "name" : "file2" }                                                                                            
{ "_id" : ObjectId("571e5a937e88d30b20b7857e"), "name" : "file22" } 

What is expected is (alphabetic / natural order)

{ "_id" : ObjectId("571e5a787e88d30b20b7857c"), "name" : "file1" }                                                                                            
{ "_id" : ObjectId("571e5a977e88d30b20b7857f"), "name" : "file2" }                                                                                           
{ "_id" : ObjectId("571e5a8c7e88d30b20b7857d"), "name" : "file11" }
{ "_id" : ObjectId("571e5a937e88d30b20b7857e"), "name" : "file22" }

As per my finding, there are other ways to sort like using aggregate + $project and $meta: "textScore", but I haven't succeeded so far.

UPDATE: An application of this problem: sort the folders / files by names

Answer 1

MongoDB doesn't provide a way to do this out of the box, but you still have two options:

The first is a client-side processing using the Array.prototype.sort method to sort the array result.

db.mydata.find().toArray().sort((a, b) => { 
    var x = Number(a.name.match(/\d+/g)[0]); 
    var y = Number(b.name.match(/\d+/g)[0]);
    return x === y ? 0 :( x < y ? -1 : 1 );
})

---

The second which is what I suggest you to do is normalize your documents with an extra field that hold the the digits in the "name" as integer and sort your documents using that value. This means that, you will need to update your documents in order to add that field, and the best way to do this is using the $set update operator and "bulk operations" for maximum efficiency. That being said, from MongoDB server version 3.2 you need to use the collection.bulkWrite method to accomplish this.

var requests = [];

db.mydata.find({}, { "name": 1 } ).forEach(doc => { 
    var fileId = Number(doc.name.match(/\d+/g)[0]); // return number from "name" value
    requests.push({
        "updateOne": { 
            "filter": { "_id": doc._id }, 
            "update": { "$set": { "fileId": fileId } } 
        } 
    }); 
    // Execute per 1000 operations and re-init the requests queue
    if( requests.length === 1000 ) 
        db.mydata.bulkWrite(requests); 
})

// Clean up queues
if (requests.length > 0) 
    db.mydata.bulkWrite(requests);

---

From MongoDB server version 2.6 you need to use the now deprecated Bulk API.

var bulk = db.mydata.initializeUnorderedBulkOp();
var count = 0;

db.collection.find({}, { "name": 1 }).forEach(function(doc) {
    var fileId = Number(doc.name.match(/\d+/g)[0]); 
    bulk.find({"_id": doc._id}).updateOne({ 
        "$set": { "fileId": fileId } 
    });
    count++;
    if (count % 1000 === 0) {
        bulk.execute();
        bulk = db.mydata.initializeUnorderedBulkOp();
    }
})

if (count > 0) 
    bulk.execute();

---

From MongoDB server version 2.4 onwards you need a different approach.

db.collection.find({}, { "name": 1 }).forEach(function(doc) {
    var fileId = Number(doc.name.match(/\d+/g)[0]); 
    db.collection.update(
        { "_id": doc._id },
        {"$set": { "fileId": fileId } } 
    );
})

---

After any of this operation, your documents now look like this:

{ "_id" : ObjectId("571e5a787e88d30b20b7857c"), "name" : "file1", "fileId" : 1 }
{ "_id" : ObjectId("571e5a8c7e88d30b20b7857d"), "name" : "file11", "fileId" : 11 }
{ "_id" : ObjectId("571e5a977e88d30b20b7857f"), "name" : "file2", "fileId" : 2 }
{ "_id" : ObjectId("571e5a937e88d30b20b7857e"), "name" : "file22", "fileId" : 22 }

---

Now, you can easily sort your documents using the .sort method.

db.mydata.find({}, { "name": 1 } ).sort( { "fileId": 1 } )

which produces the following result:

{ "_id" : ObjectId("571e5a787e88d30b20b7857c"), "name" : "file1" }
{ "_id" : ObjectId("571e5a977e88d30b20b7857f"), "name" : "file2" }
{ "_id" : ObjectId("571e5a8c7e88d30b20b7857d"), "name" : "file11" }
{ "_id" : ObjectId("571e5a937e88d30b20b7857e"), "name" : "file22" }