In MongoDB, how do I find distinct values of a large, sharded collection?
<p>I have a large mongodb collection:</p>

<ul>
<li>with 3 shards,   </li>
<li>Totalling 300M records (at least)</li>
<li>Shard key is (field1:1,field2:1)</li>
<li>There are other non-indexed fields.    </li>
<li>Field1 is a ~200-characters string  </li>
<li>Field2 is an int.    </li>
<li>There are about 10M distinct values of Field1, with more added all the time.</li>
</ul>

<p><strong>PART 1:  DISTINCT VALUES</strong></p>

<p>I need to find all the distinct values of field1.  </p>

<p>Calling db.myCollection.distinct("field1") fails because there are more than 16MB of data in the result set.</p>

<p>Since the shard key is an index, this should be a covered query.</p>

<p><strong>PART 2:  RETURN SORTED RESULTS</strong></p>

<p>Presuming there's an answer for the above, I'd like to make this recover from failures, that is, return results in sorted order.  It's not vital.</p>

<p>The goal of sorted output:  if the query fails halfway thru, I can resume from where I left off by adding the query specifier of field1:{$gt:lastGoodValue}.  </p>

<p>So: Is this possible?  Easy?  Do I have to aggregate or map-reduce?  Currently, I'm iterating over all 300M records and it shoves a lot of data around unnecessarily.</p>

<p>A collection distinct command (<a href="https://docs.mongodb.com/manual/reference/command/distinct/#dbcmd.distinct%20link" rel="nofollow">doc link</a>) returns a single variable, an array. This variable is sent as a BSON document, which has 16MB max size limit in MongoDB.</p>

<p>Having the result set in an array is convenient for some cases but if it isn't sure to fit in 16MB then you wont be able to make use of it, as you found.</p>

<h2>Part 1 answer</h2>

<p>Instead you can <a href="https://docs.mongodb.com/manual/reference/operator/aggregation/group/#retrieve-distinct-values" rel="nofollow" title="retrieve distinct values">retrieve distinct values</a> using a $group stage in a aggregation command. You can also use MapReduce, but aggregation has better performance so I'll focus on that.</p>

<pre><code>db.myCollectoin.aggregate( [ { $group : { _id : "$myField" } } ] )
</code></pre>

<p>This will change the result from being a single array variable to being a cursor, the same as normal query / find command. So the way you iterate the distinct values client-side will be different, but you can keep on fetching more and more values until the cursor is finished.</p>

<p>Use the same command whether you have a cluster, replica set, or a standalone mongod. An important performance consideration is whether or not the field(s) being distinctly grouped are indexed, but as you mention this field is the leading field in a shard key, we know that it is.</p>

<h2>Part 2 answer</h2>

<p>Yes, you can sort it. Add a $sort stage after the $group.</p>

<pre><code>db.myCollection.aggregate( [ 
  { $group : { _id : "$myField" } }, 
  { $sort: { "_id": 1 } } 
] )
</code></pre>

<p>If you have to restart the query again from a certain point you would add a <a href="https://docs.mongodb.com/manual/reference/operator/aggregation/match/%20%22$match%22%20stage" rel="nofollow">$match stage</a> as the first operation in the aggregation pipeline. E.g. <code>{ $match: {"myField": { "$gt": "AbCdEf...."} } },</code></p>

<p><em><strong>Note for new users of aggregation</strong></em>: the second "_id" in the $sort stage above is the "_id" field output by the $group stage, i.e. the distinct "myField" values. It is not a sort by the "_id" values in underlying collection.</p>

<p>Using a $project stage can rename that middle-stage "_id" key name if you'd prefer to.</p>

<pre><code>db.myCollection.aggregate( [ 
  { $group : { _id : "$myField" } }, 
  { $project : { 
     "_id" : false, /*stop it appearing as "_id" */
     "myField" : "$_id" /*put original field name "myField" back on*/
  } }, 
  { $sort: { "myField": 1 } } 
] )
</code></pre>

python mongodb-query