CODEWITHSUNDEEP
node.jsmongodbexpress
Enrico Florentino
Enrico Florentino

Reputation: 105

sort on number that is embedded in a string field in mongodb collection

I have data stored like this in my mongo db collection.

{
  "_id": ObjectId
  "username": "username-7"
},
{
  "_id": ObjectId
  "username": "username-1"
},
{
  "_id": ObjectId
  "username": "username-5"
},

Is there a way of constructing a query that can sort the entries based on number that is postfixed to the username-?

Using Node.js + Express

Upvotes: 1

Views: 904

Answers (1)

turivishal
turivishal

Reputation: 36144

There is no straight way to do this, you can try a custom logic using aggregation pipeline query,

  • $split to split string by "-" and it will return an array
  • $last to get the last element that is number from the above split result
  • $toInt to convert above return number to an integer type
  • $sort to sort the result by uid in ascending order, 1 is for ascending and -1 is for descending
db.collection.aggregate([
  {
    $addFields: {
      uid: {
        $toInt: {
          $last: { $split: ["$username", "-"] }
        }
      }
    }
  },
  { $sort: { uid: 1 } }
])

Playground

Upvotes: 1

Related Questions