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. This request is Jira ticket SERVER-7528. 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 documents that match your condition, in order specified in the input array "order", and 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.
Lots of great new things coming in 3.4 - I'll post some more tricks soon.
> 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 documents that match your condition, in order specified in the input array "order", and 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.
Lots of great new things coming in 3.4 - I'll post some more tricks soon.
RSS Feed
