CODEWITHSUNDEEP
mongodbsorting
BorisOkunskiy
BorisOkunskiy

Reputation: 1828

Mongo: sort documents by array of ids

Say you have an array of document ids stored elsewhere (e.g. in Redis sorted set).

What is the most efficient way of querying Mongo documents with { _id: { $in: ids }} and having the results sorted in the same ordering as it was in ids array?

Example

var ids = [3,2,1,6,7];

db.records.find({ _id: { $in: ids }})
 .sort({ 
   // ???
 });

// expecting [{_id: 3}, {_id: 2}, {_id: 1}, {_id: 6}, {_id: 7}]

P.S. I know I can do the sorting in application, but I wonder, if it could be done with more efficiency at the backend.

Upvotes: 18

Views: 3681

Answers (2)

Muhammad Waqas Ilyas
Muhammad Waqas Ilyas

Reputation: 21

Sometimes when performing a MongoDB query with a long "$in" list, you might want to get return documents in the same order as the elements of the "$in" array are in. Upcoming version 3.4 adds many cool new features, and some of the newly available aggregation stages and expressions make it pretty easy to do this. Example of our collection:

{ "_id" : ObjectId("580e51fc87a0572ee623854f"), "name" : "Asya" }
{ "_id" : ObjectId("580e520087a0572ee6238550"), "name" : "Charlie" }
{ "_id" : ObjectId("580e520587a0572ee6238551"), "name" : "Tess" }
{ "_id" : ObjectId("580e520887a0572ee6238552"), "name" : "David" }
{ "_id" : ObjectId("580e520c87a0572ee6238553"), "name" : "Kyle" }
{ "_id" : ObjectId("580e521287a0572ee6238554"), "name" : "Aly" }

The query we want to run is one that will return all documents where name is one of "David", "Charlie" or "Tess" and we want them in that exact order.

db.people.find({"name":{"$in": ["David", "Charlie", "Tess"]}}).sort({ ??? })

Let's define a variable called "order" so we don't have to keep typing the names in the array:

order = [ "David", "Charlie", "Tess" ]

Here's how we can do this with aggregation framework:

​

m = { "$match" : { "name" : { "$in" : order } } };
a = { "$addFields" : { "__order" : { "$indexOfArray" : [ order, "$name" ] } } };
s = { "$sort" : { "__order" : 1 } };
db.people.aggregate( [ m, a, s ] );

​Our result:

{ "_id" : ObjectId("580e520887a0572ee6238552"), "name" : "David", "__order" : 0 }
{ "_id" : ObjectId("580e520087a0572ee6238550"), "name" : "Charlie", "__order" : 1 }
{ "_id" : ObjectId("580e520587a0572ee6238551"), "name" : "Tess", "__order" : 2 }

The "$addFields" stage is new in 3.4 and it allows you to "$project" new fields to existing documents without knowing all the other existing fields. The new "$indexOfArray" expression returns position of particular element in a given array.

The result of this aggregation will be documented that match your condition, in order specified in the input array "order". The documents will include all original fields, plus an additional field called "__order". If we want to remove this field, 3.4 allows "$project" stage with just exclusion specification, so we would just add { "$project": {"__order":0}} at the end of our pipeline.

http://www.kamsky.org/stupid-tricks-with-mongodb/using-34-aggregation-to-return-documents-in-same-order-as-in-expression

Upvotes: 1

Parvin Gasimzade
Parvin Gasimzade

Reputation: 26012

You cannot do it in MongoDB right now. There is an open issue in jira.

SERVER-7528

Upvotes: 10

Related Questions