What is the maximum number of parameters passed to $in query in MongoDB?

Question

What is the maximum number of parameters passed to $in query in MongoDB?

Answer 1

The query itself is a document . MongoDB limits document sizes (as of version 2.4.0+) to 16 MB.

Really, what you're doing with a find is:

db.collectionName.find(queryDoc) 

where 'queryDoc' is something like:

{ 'fieldOne' : { $in : [ 1, 2, 3, 4] } } 

To find the maximum number of values you can pass to an $in query, use the bsonsize command:

mongos> Object.bsonsize([1]) 16 mongos> Object.bsonsize({ 'fieldOne' : { $in : [ 1, 2, 3, 4] } }) 74 mongos> Object.bsonsize({ 'fieldOne' : { $in : [ 1, 2, 3, 4, 5] } }) 85 mongos> Object.bsonsize({ 'fieldOne' : { $in : [ 1, 2, 3, 4, 5, 6] } }) 96 

So, you can see that every additional integer is size 11 bytes. Not 11 bits, 11 BYTES. This is due to the way that BSON internally stores numbers as at least 64 bits each, plus the wrapper. This can be easily seen with:

mongos> Object.bsonsize({ 'fieldOne' : { $in : [ 1, 2, 3, 4, 5, 6, 69000] } }) 107 mongos> Object.bsonsize({ 'fieldOne' : { $in : [ 1, 2, 3, 4, 5, 6, 6900000] } }) 107 mongos> Object.bsonsize({ 'fieldOne' : { $in : [ 1, 2, 3, 4, 5, 6, 69000000000] } }) 107 mongos> Object.bsonsize({ 'fieldOne' : { $in : [ 1, 2, 3, 4, 5, 6, 69000000000000] } }) 107 mongos> Object.bsonsize({ 'fieldOne' : { $in : [ 1, 2, 3, 4, 5, 6, 6900000000000000] } }) 107 mongos> Object.bsonsize({ 'fieldOne' : { $in : [ 1, 2, 3, 4, 5, 6, 690000000000000000] } }) 107 mongos> Object.bsonsize({ 'fieldOne' : { $in : [ 1, 2, 3, 4, 5, 6, 69000000000000000000] } }) 107 mongos> Object.bsonsize({ 'fieldOne' : { $in : [ 1, 2, 3, 4, 5, 6, 6900000000000000000000] } }) 107 mongos> Object.bsonsize({ 'fieldOne' : { $in : [ 1, 2, 3, 4, 5, 6, 69000000000000000000000000] } }) 107 

So, no matter what the size of an individual number, it's the same bsonsize.

On to the Question Itself: How big is that query document?

Adding these up for a one field query with an $in clause, in pymongo, through the mongos javascript prompt, whatever, all yeild the same addition facts for the maximum size of an $in query:

mongos> Object.bsonsize({ 'a' : { '$in' : [1] }}) 34 mongos> Object.bsonsize({ '' : { '$in' : [1] }}) 33 mongos> Object.bsonsize({ '' : { '$in' : [] }}) 22 

• The query document itself is 22 bytes;
• Each byte of the field name adds a single byte;
• Each number added to the $in clause adds 11 bytes.

So, Presuming you have a one-byte fieldname (the minimum, really), your maximum is:

mongos> 16*1024*1024 16777216 mongos> (16*1024*1024) - 22 - 1  16777193 mongos> ((16*1024*1024) - 22 -1) / 11 1525199.3636363635 

THE ANSWER: 1,525,198 (That's 1.5 million. That's pretty big.)

Answer 2

Looks like there is no limitation.

I did a small test.

1) Collection A had - 1 Million simple JSON object {id:,name:}

2) In Collection B, I loaded reference ids of collection A till I got the following exception. I could insert a reference count of 450k of max.

Exception in thread "main" com.mongodb.MongoInternalException: DBObject of size 18388885 is over Max BSON size 16777216 

3) I could send 450k of these ids as $in[id1...id450000] and pull the whole list of 450k ids from 1 Million objects in collection A.

Wow! this is more more more than enough for my application :D. MongoDB is really cool.