Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MongoDb: $sort by $in

I am running a mongodb find query with an $in operator:

collection.find({name: {$in: [name1, name2, ...]}})

I would like the results to be sorted in the same order as my name array: [name1, name2, ...]. How do I achieve this?

Note: I am accessing MongoDb through pymongo, but I don't think that's of any importance.

EDIT: as it's impossible to achieve this 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"]))
like image 216
Régis B. Avatar asked Feb 01 '13 15:02

Régis B.


3 Answers

You can achieve this with aggregation framework starting with upcoming version 3.4 (Nov 2016).

Assuming the order you want is the array order=["David", "Charlie", "Tess"] you do it via this pipeline:

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

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

like image 75
Asya Kamsky Avatar answered Oct 16 '22 13:10

Asya Kamsky


Impossible. $in operator checks the presence. The list is treated as set.

Options:

  • Split for several queries for name1 ... nameN or filter the result the same way. More names - more queries.
  • Use itertools groupby/ifilter. In that case - add the "sorting precedence" flag to every document and match name1 to PREC1, name2 to PREC2, ...., then isort by PREC then group by PREC.

If your collection has the index on "name" field - option 1 is better. If doest not have the index or you cannot create it due to high write/read ratio - option 2 is for you.

like image 39
Vitaly Greck Avatar answered Oct 16 '22 13:10

Vitaly Greck


Vitaly is correct it's impossible to do that with find 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 will hint other people in the right direction.

like image 44
agenteo Avatar answered Oct 16 '22 11:10

agenteo