Mongo sort by string value that is actually number

问题:

I have collection that contains objects such as this:

{ 
    "_id" : ObjectId("57f00cf47958af95dca29c0c"), 
    "id" : "...", 
    "threadId" : "...", 
    "ownerEmail" : "...@...", 
    "labelIds" : [
       ...
    ], 
    "snippet" : "...", 
    "historyId" : "35699995", 
    "internalDate" : "1422773000000", 
    "headers" : {
        "from" : "...@...", 
        "subject" : "....", 
        "to" : "...@..."
    }, 
    "contents" : {
        "html" : "...."
    }
}

When accessing objects, I want to sort them by iternalDate value, which was supposed to be integer, however it is a string. Is there a way to sort them when fetching even if these are strings? By alphabetic order? Or is there a way to convert them to integer painlessly?

回答1:

It seems to me that the best solution here would be to parse it first as an integer. You could do it using a simple script in javascript like this, using the mongodb client for node:

db.collection.find({}, {internalDate: 1}).forEach(function(doc) {
    db.collection.update(
       { _id: doc._id },
       { $set: { internalDate: parseInt(doc.internalDate) } }
    )
})

回答2:

Collation is what you need...

db.collection.find()
  .sort({internalDate: 1})
  .collation({locale: "en_US", numericOrdering: true})

回答3:

you also can use the aggregate method to sort number which is actually a string.

 db.collection.aggregate([{
     $sort : {
          internalDate : 1
     }
 }], {
     collation: {
         locale: "en_US",
         numericOrdering: true
     }
 });

if you are using mongoose-paginate package for serverside pagination .. so don't use this package, use only mongoose-paginate-v2 for serverside pagination. this package for nodejs side

回答4:

I was having this issue. I use string length to sort first and then apply the sort of my numeric value stored like a string. e.g. "1", "100", "20", "3" that should be sorted like 1, 3, 29, 100.

    db.AllTours.aggregate([
    {
        $addFields : {
            "MyStringValueSize" : { $strLenCP: "$MyValue" }
        }
    },
    { 
        $sort : { 
            "MyStringValueSize" : 1,
            "MyValue" : 1
        } 
    }
    ]);

There is a new feature in version 4.0 called $toInt that can be used to parse your string and then sort. In my case I can't upgrade from 3.6.

回答5:

This is my solution and it worked for me

db.getCollection('myCollection').aggregate([
{
    $project: {
        newMonth: {
            $cond: { if: {
                $and: [
                    {$ne: ['$month', '10']},
                    {$ne: ['$month', '11']},
                    {$ne: ['$month', '12']},
                ]
            }, then: {$concat: ['0', '$month']}, else: '$month' }
        }
        }
    },

    {
        $sort: {newMonth: -1}
    }
])