title: Iterating over distinct items in one field in MongoDB
body: <p>I have a very large collection (~7M items) in MongoDB, primarily consisting of documents with three fields.</p>
  
  <p>I'd like to be able to iterate over all the <em>unique</em> values for one of the fields, in an expedient manner.</p>
  
  <p>Currently, I'm querying for just that field, and then processing the returned results by iterating on the cursor for uniqueness. This works, but it's rather slow, and I suspect there must be a better way.</p>
  
  <p>I know mongo has the <code>db.collection.distinct()</code> function, but this is limited by the maximum BSON size (16 MB), which my dataset exceeds.</p>
  
  <p>Is there any way to iterate over something similar to the <code>db.collection.distinct()</code>, but using a cursor or some other method, so the record-size limit isn't as much of an issue? </p>
  
  <p>I think maybe something like the map/reduce functionality would possibly be suited for this kind of thing, but I don't really understand the map-reduce paradigm in the first place, so I have no idea what I'm doing. The project I'm working on is partially to learn about working with different database tools, so I'm rather inexperienced.</p>
  
  <p><sub>I'm using PyMongo if it's relevant (I don't think it is). This should be mostly dependent on MongoDB alone.</sub></p>
  
  <hr>
  
  <p>Example:</p>
  
  <p>For this dataset:  </p>
  
  <pre><code>{"basePath" : "foo", "internalPath" : "Neque", "itemhash": "49f4c6804be2523e2a5e74b1ffbf7e05"}
  {"basePath" : "foo", "internalPath" : "porro", "itemhash": "ffc8fd5ef8a4515a0b743d5f52b444bf"}
  {"basePath" : "bar", "internalPath" : "quisquam", "itemhash": "cf34a8047defea9a51b4a75e9c28f9e7"}
  {"basePath" : "baz", "internalPath" : "est", "itemhash": "c07bc6f51234205efcdeedb7153fdb04"}
  {"basePath" : "foo", "internalPath" : "qui", "itemhash": "5aa8cfe2f0fe08ee8b796e70662bfb42"}
  </code></pre>
  
  <p>What I'd like to do is iterate over <em>just</em> the <code>basePath</code> field. For the above dataset, this means I'd iterate over <code>foo</code>, <code>bar</code>, and <code>baz</code> just once each.</p>
  
  <p>I'm not sure if it's relevant, but the DB I have is structured so that while each field is not unique, the aggregate of all three is unique (this is enforced with an index). </p>
  
  <hr>
  
  <p>The query and filter operation I'm currently using (note: I'm restricting the query to a subset of the items to reduce processing time):  </p>
  
  <pre><code>    self.log.info("Running path query")
      itemCursor = self.dbInt.coll.find({"basePath": pathRE}, fields={'_id': False, 'internalPath': False, 'itemhash': False}, exhaust=True)
      self.log.info("Query complete. Processing")
      self.log.info("Query returned %d items", itemCursor.count())
      self.log.info("Filtering returned items to require uniqueness.")
      items = set()
      for item in itemCursor:
          # print item
          items.add(item["basePath"])
  
      self.log.info("total unique items = %s", len(items))
  </code></pre>
  
  <p>Running the same query with <code>self.dbInt.coll.distinct("basePath")</code> results in <code>OperationFailure: command SON([('distinct', u'deduper_collection'), ('key', 'basePath')]) failed: exception: distinct too big, 16mb cap</code></p>
  
  <hr>
  
  <p>Ok, here is the solution I wound up using. I'd add it as an answer, but I don't want to detract from the actual answers that got me here.</p>
  
  <pre><code>    reStr = "^%s" % fqPathBase
      pathRE = re.compile(reStr)
      self.log.info("Running path query")
  
      pipeline = [
          { "$match" :
              {
                  "basePath" : pathRE
              }
          },
          # Group the keys
          {"$group":
              {
                  "_id": "$basePath"
              }
          },
  
          # Output to a collection "tmp_unique_coll"
          {"$out": "tmp_unique_coll"}
          ]
  
      itemCursor = self.dbInt.coll.aggregate(pipeline, allowDiskUse=True)
      itemCursor = self.dbInt.db.tmp_unique_coll.find(exhaust=True)
  
      self.log.info("Query complete. Processing")
      self.log.info("Query returned %d items", itemCursor.count())
      self.log.info("Filtering returned items to require uniqueness.")
      items = set()
      retItems = 0
      for item in itemCursor:
          retItems += 1
          items.add(item["_id"])
  
  
      self.log.info("Recieved items = %d", retItems)
      self.log.info("total unique items = %s", len(items))
  </code></pre>
  
  <p>General performance compared to my previous solution is about 2X in terms of wall-clock time. On a query that returns 834273 items, with 11467 uniques:  </p>
  
  <p>Original method(retreive, stuff into a python <code>set</code> to enforce uniqueness):  </p>
  
  <pre><code>real    0m22.538s
  user    0m17.136s
  sys     0m0.324s
  </code></pre>
  
  <p>Aggregate pipeline method :</p>
  
  <pre><code>real    0m9.881s
  user    0m0.548s
  sys     0m0.096s
  </code></pre>
  
  <p>So while the overall execution time is only ~2X better, the aggregation pipeline is massively more performant in terms of actual CPU time.</p>
  
  <hr>
  
  <p>Update:</p>
  
  <p>I revisited this project recently, and rewrote the DB layer to use a SQL database, and everything was much easier. A complex processing pipeline is now a simple <code>SELECT DISTINCT(colName) WHERE xxx</code> operation.</p>
  
  <p>Realistically, MongoDB and NoSQL databases in general are vary much the wrong database type for what I'm trying to do here. </p>