CODEWITHSUNDEEP
mongodbindexing
Khaled Abu Shamat
Khaled Abu Shamat

Reputation: 1134

mongodb query sorting and indexing

I have a collection of documents in mongodb, and I want to query some ids, let's say: 2, 1, 3. The query returns the result in a random way, but I want it to be returned in the same way that it was queried (2, 1, 3). I added an index and tried to sort on the index, but the sort order still isn't correct. Here is a sample of my documents:

{  
   "_id":{  
      "_id":"90113"
   }
}

And here is the query that I'm using:

{"_id._id": {"$in" : ["348410","90113","69186"]}}

Expected result:

{
    _id._id: "348410",
    _id._id: "90113",
    _id._id: "69186"
}

current sort query:

{'_id._id':1}

Upvotes: 0

Views: 67

Answers (1)

klhr
klhr

Reputation: 3380

Note: this answer is incorrect. Leaving it because there's clarifying discussion in the comments.

It looks like you're doing it correctly. Keep in mind that if you're storing values as strings, mongo will sort it in lexicographical order (i.e. "10" will come before "2") rather than in numerical order (where 2 comes before 10).

> for (let i = 0; i < 15; i++) { db.example.insert({i: "" + i}) }
WriteResult({ "nInserted" : 1 })
> db.example.find({}).sort({i: 1});
{ "_id" : ObjectId("5d27111931deb3f658760d4a"), "i" : "0" }
{ "_id" : ObjectId("5d27111931deb3f658760d4b"), "i" : "1" }
{ "_id" : ObjectId("5d27111931deb3f658760d54"), "i" : "10" }
{ "_id" : ObjectId("5d27111931deb3f658760d55"), "i" : "11" }
{ "_id" : ObjectId("5d27111931deb3f658760d56"), "i" : "12" }
{ "_id" : ObjectId("5d27111931deb3f658760d57"), "i" : "13" }
{ "_id" : ObjectId("5d27111931deb3f658760d58"), "i" : "14" }
{ "_id" : ObjectId("5d27111931deb3f658760d4c"), "i" : "2" }
{ "_id" : ObjectId("5d27111931deb3f658760d4d"), "i" : "3" }
{ "_id" : ObjectId("5d27111931deb3f658760d4e"), "i" : "4" }
{ "_id" : ObjectId("5d27111931deb3f658760d4f"), "i" : "5" }
{ "_id" : ObjectId("5d27111931deb3f658760d50"), "i" : "6" }
{ "_id" : ObjectId("5d27111931deb3f658760d51"), "i" : "7" }
{ "_id" : ObjectId("5d27111931deb3f658760d52"), "i" : "8" }
{ "_id" : ObjectId("5d27111931deb3f658760d53"), "i" : "9" }

If you'd like to sort these numbers numerically, you can use an aggregation (in mongo 4x) to convert them to integers first & then sort them:

db.example.aggregate([{$project: {int: {"$toInt": "$i"}}}, {$sort: {"int": 1}}])
{ "_id" : ObjectId("5d2711d8834199b3de049d8d"), "int" : 0 }
{ "_id" : ObjectId("5d2711d8834199b3de049d8e"), "int" : 1 }
{ "_id" : ObjectId("5d2711d8834199b3de049d8f"), "int" : 2 }
{ "_id" : ObjectId("5d2711d8834199b3de049d90"), "int" : 3 }
{ "_id" : ObjectId("5d2711d8834199b3de049d91"), "int" : 4 }
{ "_id" : ObjectId("5d2711d8834199b3de049d92"), "int" : 5 }
{ "_id" : ObjectId("5d2711d8834199b3de049d93"), "int" : 6 }
{ "_id" : ObjectId("5d2711d8834199b3de049d94"), "int" : 7 }
{ "_id" : ObjectId("5d2711d8834199b3de049d95"), "int" : 8 }
{ "_id" : ObjectId("5d2711d8834199b3de049d96"), "int" : 9 }
{ "_id" : ObjectId("5d2711d8834199b3de049d97"), "int" : 10 }
{ "_id" : ObjectId("5d2711d8834199b3de049d98"), "int" : 11 }
{ "_id" : ObjectId("5d2711d8834199b3de049d99"), "int" : 12 }
{ "_id" : ObjectId("5d2711d8834199b3de049d9a"), "int" : 13 }
{ "_id" : ObjectId("5d2711d8834199b3de049d9b"), "int" : 14 }

Realistically though, it's probably best to update all of your _ids so that they're numbers rather than strings (or just regular mongo _ids). how to convert string to numerical values in mongodb has an example of how to do the conversion

Upvotes: 1

Related Questions