title: server side set intersection in mongodb
body: <p>In an application I am working on, a requirement is to do massive set intersection, to the tune of 10-1,000,000 items or so.  The items that we are intersecting are simply ObjectId's.</p>
  
  <p>So for instance there is a boxes document and inside the boxes document there is an item_ids Array.  This item_ids array for each box holds 10-1,000,000 ObjectId's.</p>
  
  <p>The end goal here is to say, given box A with ObjectId 4d3dc3898951498107000005, and box B with ObjectId 4d3dc3898951498107000002, which item_ids do they have in common?</p>
  
  <p>Here is how im doing it:</p>
  
  <pre><code>db.boxes.distinct("item_ids", {'_id' : {$in : [ObjectId("4d3dc3898951498107000005"), ObjectId("4d3dc3898951498107000002")]}})
  </code></pre>
  
  <p>Firstly just curious if this seems like a sane approach.  In my research so far it seems like map reduce is a common suggestion for large intersections, but that it is not recommended for realtime queries.  </p>
  
  <p>Secondly, curious how this would behave in a sharded environment?  Will mongos run a chunk of the query on the mongod's it needs to and aggregate my result magically?</p>
  
  <p>Lastly, if the above is sane, is it also sane to do:</p>
  
  <pre><code>db.items.find({'_id' : { $in : db.eval(function() {return db.boxes.distinct("item_ids", {_id:{$in:[ObjectId("4d3dc3898951498107000005"), ObjectId("4d3dc3898951498107000002")]}}); }) }}) 
  </code></pre>
  
  <p>Which would basically be finding which items both box A and box B have in common, and then materializing them into objects all in one server side query.  This appears to also work with .limit and .skip to effectively implement a paging of the data set.</p>
  
  <p>Anyhow, any feedback is valuable, thanks!</p>