mongodb collection sort string filed by numeric order-CodePudding

I have collection with numeric value in string format. I want to get distinct value sorted by numeric order.

Here is the sample data and query I have https://mongoplayground.net/p/Z9VRzlHjD7J

Data

[
  {
    id: "1"
  },
  {
    id: "1005"
  },
  {
    id: "50"
  },
  {
    id: "110"
  },
  {
    id: "1157"
  },
  {
    id: "9"
  },
  {
    id: "447"
  },
  
]

Query:

db.collection.aggregate([
  {
    $group: {
      _id: "$id"
    }
  },
  {
    $sort: {
      _id: 1
    }
  }
])

output

[
  {
    "_id": "1"
  },
  {
    "_id": "1005"
  },
  {
    "_id": "110"
  },
  {
    "_id": "1157"
  },
  {
    "_id": "447"
  },
  {
    "_id": "50"
  },
  {
    "_id": "9"
  }
]

How can I sort this value by numeric order?

Thanks

CodePudding user response：

You can use Collation to sort numeric strings as numbers in MongoDB. 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.

Example:

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

CodePudding user response：

db.collection.aggregate([
  {
    "$project": {
      intId: {
        $toInt: "$id"
      }
    }
  },
  {
    $sort: {
      intId: 1
    }
  }
])