0

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}
klhr
  • 3,300
  • 1
  • 11
  • 25
Khaled Jamal
  • 628
  • 1
  • 6
  • 18

1 Answers1

1

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

klhr
  • 3,300
  • 1
  • 11
  • 25
  • im not looking to get them in numbers order or string order, i want to sort them according to the query for example this query {"_id._id": {"$in" : ["348410","90113","69186"]}} should return : 348410, 90113, 69186 – Khaled Jamal Jul 11 '19 at 10:44
  • As far as I know, that's not possible with regular queries; I think you'll either have to use aggregations or the map reduce framework, and I suspect that your best option will be to sort those documents application side. If you're trying to do this with a limit, sorting them application side won't work, so I think the best option would be querying each member of the `$in` serially. – klhr Jul 11 '19 at 11:12
  • For the aggregation, I think you'd want to use `$switch` to assign sorting values to the documents & then sort based on those values. I'm much less familiar with map reduce (and it tends to be much slower than the aggregation pipeline, so if it's possible to do with an aggregation, it's generally best to do it that way) – klhr Jul 11 '19 at 11:15
  • i did it in aggregation but its too slow – Khaled Jamal Jul 11 '19 at 11:40
  • In that case, if there's an order that you already know you'll want to get documents in ahead of time, I'd try adding that to the documents. If that's not the case & application side sorting the full result set doesn't work, I'd try doing separate queries with a sort & limit (perhaps even with a projection to limit the fields returned) and do a merge sort application-side. – klhr Jul 11 '19 at 11:48