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

by

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

More Related Contents:

  1. Get names of all keys in the collection
  2. How to filter array in subdocument with MongoDB [duplicate]
  3. How to select a single field for all documents in a MongoDB collection?
  4. mongodb group values by multiple fields
  5. Conditional $sum in MongoDB
  6. mongoDB/mongoose: unique if not null
  7. MongoDB password with “@” in it
  8. MongoDB: How to query for records where field is null or not set?
  9. exclude fields in $lookup aggregation
  10. Add some kind of row number to a mongodb aggregate command / pipeline
  11. How do you query for “is not null” in Mongo?
  12. MongoDB: exception in initAndListen: 20 Attempted to create a lock file on a read-only directory: /data/db, terminating
  13. MongoDB 3.2 authentication failed
  14. MongoDB ‘unable to find index for $geoNear query’
  15. Does Mongoose support the Mongodb `findAndModify` method?
  16. MongoDB Text Search AND multiple search words
  17. How does sorting with an index work in MongoDB?
  18. How to get multiple document using array of MongoDb id?
  19. querying a collection without passing schema in mongoose
  20. Update a value in MongoDB based on its current value
  21. How to connect to external MongoDB instance in Meteor?
  22. Find documents whose array field contains at least n elements of a given array
  23. Generating a Structure for Aggregation
  24. MongoDB via Mongoose JS – What is findByID?
  25. mongo: command not found on mongodb 6.0 docker container [duplicate]
  26. Mongodb: Perform a Date range query from the ObjectId in the mongo shell
  27. MongoDB server can still be accessed without credentials
  28. Mongodb, aggregate query with $lookup
  29. Mongo unique index case insensitive
  30. Return the last “true” value for each group

Leave a Comment