Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does MongoDB's $in clause guarantee order

When using MongoDB's $in clause, does the order of the returned documents always correspond to the order of the array argument?

like image 578
user2066880 Avatar asked Apr 01 '14 22:04

user2066880


1 Answers

As noted, the order of the arguments in the array of an $in clause does not reflect the order of how the documents are retrieved. That of course will be the natural order or by the selected index order as shown.

If you need to preserve this order, then you basically have two options.

So let's say that you were matching on the values of _id in your documents with an array that is going to be passed in to the $in as [ 4, 2, 8 ].

Approach using Aggregate


var list = [ 4, 2, 8 ];  db.collection.aggregate([      // Match the selected documents by "_id"     { "$match": {         "_id": { "$in": [ 4, 2, 8 ] },     },      // Project a "weight" to each document     { "$project": {         "weight": { "$cond": [             { "$eq": [ "$_id", 4  ] },             1,             { "$cond": [                 { "$eq": [ "$_id", 2 ] },                 2,                 3             ]}         ]}     }},      // Sort the results     { "$sort": { "weight": 1 } }  ]) 

So that would be the expanded form. What basically happens here is that just as the array of values is passed to $in you also construct a "nested" $cond statement to test the values and assign an appropriate weight. As that "weight" value reflects the order of the elements in the array, you can then pass that value to a sort stage in order to get your results in the required order.

Of course you actually "build" the pipeline statement in code, much like this:

var list = [ 4, 2, 8 ];  var stack = [];  for (var i = list.length - 1; i > 0; i--) {      var rec = {         "$cond": [             { "$eq": [ "$_id", list[i-1] ] },             i         ]     };      if ( stack.length == 0 ) {         rec["$cond"].push( i+1 );     } else {         var lval = stack.pop();         rec["$cond"].push( lval );     }      stack.push( rec );  }  var pipeline = [     { "$match": { "_id": { "$in": list } }},     { "$project": { "weight": stack[0] }},     { "$sort": { "weight": 1 } } ];  db.collection.aggregate( pipeline ); 

Approach using mapReduce


Of course if that all seems to hefty for your sensibilities then you can do the same thing using mapReduce, which looks simpler but will likely run somewhat slower.

var list = [ 4, 2, 8 ];  db.collection.mapReduce(     function () {         var order = inputs.indexOf(this._id);         emit( order, { doc: this } );     },     function() {},     {          "out": { "inline": 1 },         "query": { "_id": { "$in": list } },         "scope": { "inputs": list } ,         "finalize": function (key, value) {             return value.doc;         }     } ) 

And that basically relies on the emitted "key" values being in the "index order" of how they occur in the input array.


So those essentially are your ways of maintaining the order of a an input list to an $in condition where you already have that list in a determined order.

like image 144
Neil Lunn Avatar answered Sep 28 '22 09:09

Neil Lunn