title: How to find distinct records using aggregate query in mongodb
body: <p>Need an help with group by query in mongo db. I need to find the distinct account numbers from a collection where it matches the ID : ABC_00000000003.</p>
  
  <p>I have written below query but for some reason its not pulling all distinct records. Please correct me if anything wrong in this query.</p>
  
  <p>Appreciate your help.</p>
  
  <pre><code>db.controlDocument.aggregate([{$match:{"ID": "ABC_00000000003"}}, {$group:{"_id": "$AccountNumber"}}])
  </code></pre>
  
  <p>Before using this query i used below query but it failing when the collection contains more than 1 million records. </p>
  
  <pre><code>db.controlDocument.distinct("AccountNumber", {"ID" : "ABC_00000000003"})
  </code></pre>
  
  <p>Below is the exception i'm getting when i'm trying above distinct query in Robo mongo. And even in my java program i'm facing similary issue. In my QA DB, the collection i'm trying to query against contains 280k records and my production collection contains nearly 1 million records. Need a better query which could give the results in few secs. So i tried to written a aggregate query to get same distinct records but i'm seeing difference in the count between these 2 queries if i write query to pull some smaller result set. </p>
  
  <pre><code>Error: distinct failed: { "ok" : 0, "errmsg" : "distinct too big, 16mb cap", "code" : 17217 } :
  DBCollection.prototype.distinct@src/mongo/shell/collection.js:1650:1
  @(shell):1:1
  </code></pre>