How to find distinct records using aggregate query

[Prasanna Ganesh]

Hi,

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.

I have written below query but for some reason its not pulling all distinct records. Please correct me if anything wrong in this query.

Appreciate your help

db.controlDocument.aggregate([{$match:{"ID": "ABC_00000000003"}}, {$group:{"_id": "$AccountNumber"}}])

[Prasanna Ganesh]

Team,

Any suggestion on this. I'm posted same question in stack overflow where i have added more details.

http://stackoverflow.com/questions/42992741/how-to-find-distinct-records-using-aggregate-query-in-mongodb 

[Asya Kamsky]

There is nothing wrong with your aggregation.

Could you explain why you think that "its not pulling all distinct records"?

Asya

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/5e313e93-e808-4a02-ada2-83af1639c9f5%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Asya Kamsky
Lead Product Manager
MongoDB
Download MongoDB - mongodb.org/downloads
We're Hiring! - https://www.mongodb.com/careers

[Prasanna Ganesh]

Thank you for the response. 

To answer to your question, when i used distinct query which is below in my java program i got the result set count as 135 for a different account which has less documents in the collection. But for same account number i got result set count as 105 when i used the aggregate query. And i'm confused now, where its going wrong. When i validate the records from distinct query its looks correct to me. 

Before using this query i used below query but it failing when the collection contains more than 1 million records.

db.controlDocument.distinct("AccountNumber", {"ID" : "ABC_00000000003"})

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 similar issue. In my QA DB, the collection i'm trying to query against contains 280k records and in 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.

Error: distinct failed: { "ok" : 0, "errmsg" : "distinct too big, 16mb cap", "code" : 17217 } :
DBCollection.prototype.d

Now i need a query which is able to find distinct records from a collection where it contains million documents. Kindly advise. 

On Saturday, 25 March 2017 13:30:16 UTC-6, Asya Kamsky wrote:

There is nothing wrong with your aggregation.

Could you explain why you think that "its not pulling all distinct records"?

Asya

On Fri, Mar 24, 2017 at 11:19 AM, Prasanna Ganesh <nprasan...@gmail.com> wrote:

Team,

Any suggestion on this. I'm posted same question in stack overflow where i have added more details.

http://stackoverflow.com/questions/42992741/how-to-find-distinct-records-using-aggregate-query-in-mongodb 

On Friday, 24 March 2017 00:18:20 UTC-6, Prasanna Ganesh wrote:

Hi,

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.

I have written below query but for some reason its not pulling all distinct records. Please correct me if anything wrong in this query.

Appreciate your help

db.controlDocument.aggregate([{$match:{"ID": "ABC_00000000003"}}, {$group:{"_id": "$AccountNumber"}}])

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.

To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/5e313e93-e808-4a02-ada2-83af1639c9f5%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Rhys Campbell]

Use sort / limit or allowDiskUse...

https://docs.mongodb.com/manual/core/aggregation-pipeline-optimization/#sort-limit-coalescence

[Asya Kamsky]

You say yourself that you are comparing *different* accounts. 

I'm not aware of aggregation ever returning fewer than all distinct records. Simply not very likely or everyone would see this problem. 

Why don't you do this:

Run count for the filter on account you are working on. 

Rerun your aggregation with match and group with count:{$sum:1} added and then add up all the count values and see if they are the same as count command on the collection with same filter. 

Asya

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/e6efb575-51a8-4d65-b7ff-e9e2db4b6fff%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.