MongoDb: $ sort by $ in
I am running a mongodb request find
with the operator $in
:
collection.find({name: {$in: [name1, name2, ...]}})
I would like to see the results sorted in the same order as that of my array of names: [name1, name2, ...]
. How do you achieve this?
Note. I am accessing MongoDb via pymongo, but I don't think it matters.
EDIT: Since this cannot be done natively in MongoDb, I ended up using a typical Python solution:
names = [name1, name2, ...]
results = list(collection.find({"name": {"$in": names}}))
results.sort(key=lambda x: names.index(x["name"]))
source to share
impracticable. $ in the operator checks for the presence. The list is treated as installed.
Parameters:
- Split into multiple requests for name1 ... nameN or filter the result in the same way. More names means more requests.
- Use the itertools groupby / ifilter command. In this case, add a "sort" flag to each document and map name1 to PREC1, name2 to PREC2, .... then release PREC and then group by PREC.
If your collection has an index in the name field, option 1 is better. If you do not have an index or cannot create one due to a high read / write ratio - option 2 is for you.
source to share
Vitaly is right, this cannot be done with lookup, but it can be achieved with aggregates:
db.collection.aggregate( [ { $match: { name: {$in: [name1, name2, ...]} }, { $project: { 'name': 1, 'name1': { $eq: [ 'name1', '$name' ] }, 'name2': { $eq: [ 'name2', '$name' ] } } }, { $sort: { 'name1': 1, 'name2': 1 } } ] )
tested on 2.6.5
I hope this hints other people in the right direction.
source to share
You can achieve this with an aggregation framework starting with the next version 3.4 (Nov 2016).
Assuming the order you want is the array order=["David", "Charlie", "Tess"]
you do along this pipeline:
m = { "$match" : { "name" : { "$in" : order } } };
a = { "$addFields" : { "__order" : { "$indexOfArray" : [ order, "$name" ] } } };
s = { "$sort" : { "__order" : 1 } };
db.collection.aggregate( m, a, s );
Phase "$addFields"
is new in 3.4 and allows you to "$project"
add new fields to existing documents without knowing all the other existing fields. The new expression "$indexOfArray"
returns the position of a specific element in a given array.
The result of this aggregation will be documents that match your condition, in the order specified in the input array order
, and the documents will include all of the original fields plus an additional field called__order
source to share