Order of responses to MongoDB $in query?

Question

The MongoDB docs on the $in conditional operator don't say anything about order. If I run a query of the form

db.things.find({'_id': {'$in': id_array}});

what will be the order of the returned results? And is there a way for me to tell MongoDB "I want the results sorted so that they're in the same order as the ids in id_array?"

simple Python/PyMongo solution: http://stackoverflow.com/a/40048951/304209 — Dennis Golomazov, Oct 14 '16 at 17:35
Check [this](http://stackoverflow.com/a/42293303/4440874) answer. — Jyotman Singh, Feb 17 '17 at 08:50

score 11 · Answer 1 · edited Feb 17 '17 at 08:28

11

Asked for this feature on JIRA:

Quickly got a pretty good response: use $or instead of $in

c.find( { _id:{ $in:[ 1, 2, 0 ] } } ).toArray()

vs.

c.find( { $or:[ { _id:1 }, { _id:2 }, { _id:0 } ] } ).toArray()

Read the bug report for more info.

Update:

The $or work-around hack no longer works starting with 2.6.x - it was a side effect of implementation which has changed.

edited Feb 17 '17 at 08:28

Jyotman Singh

10,792
8
39
55

answered Nov 01 '12 at 21:09

Jonathan Ong

19,927
17
79
118

Not quite so easy/nice when you are producing the list programatically, but good that there's a solution. – UpTheCreek Feb 19 '14 at 09:54
7

This method does not seem to be working from 2.6.x, as mentioned in the JIRA comment – rlay3 Oct 19 '14 at 08:29

score 7 · Answer 2 · answered Oct 14 '14 at 15:29

I had the same problem and my solution was to create a hash map to do the mapping between my array of ids (my query) and my array of results from MongoDB.

The extra work is to browse the array of results and to insert, for each item, a new key-value pair: the key is the ID and the value is the result object.

Then, when I want to browse my results in the same order as my query was, I can use the hashmap to retrieve the correct object. No sorting, and no fancy Mongo DB option.

In Javascript it would be something like:

//The order you want
var queryIds = [ 8, 5, 3, 7 ];

//The results from MongoDB in an undefined order
var resultsFromMongoDB = [
    {_id: 7, data: "d" },
    {_id: 8, data: "a" },
    {_id: 5, data: "b" },
    {_id: 3, data: "c" },
];

//The function to create a hashmap to do the mapping
function createHashOfResults( results ){
    var hash = {};

    for( var i = 0 ; i < results.length ; i++ ){
        var item = results[i];
        var id = item._id;
        hash[ id ] = item;
    }

    return hash;
}

//Let's build the hashmap
var hash = createHashOfResults( resultsFromMongoDB );

//Now we can display the results in the order we want
for( var i = 0 ; i < queryIds.length ; i++ ){
    var queryId = queryIds[i];
    var result = hash[queryId];
    console.log( result.data );
}

This will display:

a
b
c
d

@JyotmanSingh - can you give an idea of what 'very large' would mean? I need this functionality so want to know if it is a valid solution. Thank you. — user1063287, Aug 06 '20 at 04:11
@user1063287 If your goal is to get the results in order you can have a look at this solution: https://stackoverflow.com/questions/22797768/does-mongodbs-in-clause-guarantee-order — Jason, Aug 06 '20 at 17:30

score 6 · Answer 3 · answered Jan 12 '16 at 18:34

@Jason 's answer is the correct one.

About other answers: I wouldn't recommend querying one by one because it can bring serious performance issues.

In addition to @Jason 's answer, it can be optimised using Array.reduce and Array.map methods, like this:

//The order you want
var queryIds = [8, 5, 3, 7];

//The results from MongoDB in an undefined order
var resultsFromMongoDB = [
    {_id: 7, data: "d"},
    {_id: 8, data: "a"},
    {_id: 5, data: "b"},
    {_id: 3, data: "c"}
];

var reorderedResults = naturalOrderResults(resultsFromMongoDB, queryIds);


function naturalOrderResults(resultsFromMongoDB, queryIds) {
    //Let's build the hashmap
    var hashOfResults = resultsFromMongoDB.reduce(function (prev, curr) {
        prev[curr._id] = curr;
        return prev;
    }, {});

    return queryIds.map( function(id) { return hashOfResults[id] } );
}

score 4 · Answer 4 · answered Jun 29 '10 at 16:54

4

the order of the results isn't mentioned because they won't be ordered in any dependable way. the only way to get them ordered would be to do separate queries client-side for each item in the $in array

answered Jun 29 '10 at 16:54

mdirolf

7,521
2
23
15

OK, that's what I expected. So let's say I'm making the query via PyMongo. Then I can either: 1. Make a separate MongoDB for each id in id_array and append it to the end of a list (inefficient because of the excess queries), or 2. Make the single MongoDB query given in my question, then use Python to copy the results from MongoDB into a properly ordered list (inefficient because I'm copying potentially large amounts of data on the client side)? There isn't a better way? Some kind of custom query I can run on MongoDB that will sort the results before they're returned? – Trevor Burnham Jun 29 '10 at 17:43
you could change your schema. there is actually a question going on the mongodb-user list right now discussing exactly that option, for exactly this question. should probably just check that out and follow up there. – mdirolf Jun 29 '10 at 18:49
3

I might be a bit late to this party, but I've just come across this problem. My solution was to build a dictionary of (in my case) `ObjectId` to position (that is, position in the list I used for the `$in` query), and then use that to rearrange the output. I am operating on a small number of items, though, so perhaps this isn't helpful for your case. – dcrosta Mar 10 '11 at 20:20
1

I've basically resorted to the same mechanism as @dcrosta: manually re-ordering the result using the original "in list" ordering as reference. I remember this being a problem with SQL databases as well, so not a Mongo peculiarity. – Cornel Masson Sep 15 '11 at 09:37

score 4 · Answer 5 · answered Jan 13 '16 at 17:33

4

If you don't mind using Underscore.js and aren't too worried about scale (IE, you don't mind fetch-ing rather than working with the cursor) , here's how I maintained order:

var results = db.things.find({'_id': {'$in': id_array}}).fetch(); 
return _.sortBy(results, function(thing) { 
  return id_array.indexOf(thing._id); 
});

answered Jan 13 '16 at 17:33

Alexey

41
2

i see a tradeoff here: This approach looks cleaner but it may use more CPU. This happens because array.indexOf will scan id_array for every item in your results array and after all this work it ends resorting all results. Check for yourself: https://jsfiddle.net/davidrc/gyjrtovu/ Of course this isn't a problem to front-ends (we are talking about some ms), but may put an extra load when we run this code in the backend with tons of users. – David Rissato Cruz Jan 14 '16 at 16:37
Agreed. I had to do this for <50 entries so being obvious with intent and brevity was my first priority. – Alexey Jan 16 '16 at 21:08

dB. · Answer 6 · 2014-05-29T15:07:46.207

2

The $or workaround no longer works in 2.6.x, see https://jira.mongodb.org/browse/SERVER-14083. Here's a Ruby workaround implementation: https://gist.github.com/dblock/d5ed835f0147467a6a27

edited May 29 '14 at 15:07

answered May 29 '14 at 14:54

dB.

4,700
2
46
51

8

This is kinda disappointing. – tokudu Jun 02 '14 at 23:14

Order of responses to MongoDB $in query?

6 Answers6

Linked

Related