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

Solution 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.)

Solution 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.

Solution 3:

I think the limitation is just determined by the size of a BSONDocument. When you define a query, you can keep adding values into an $in clause up until you exceed the maximum document size. So how many values you can have in the clause depends on how big each value is (the smaller the size of each value, the more you can include in the $in clause).

In terms of performance, from what I've found, there is a "sweet spot" for the number of values in an $in clause. See my answer in this related question: Is it OK to query a MongoDB multiple times per request?

i.e. balancing number of values in $in clause vs number of queries sent. I'm mid way through a blog post on that to try and dive into more detail.