mongodb distinct() in an array

Question

In my mongodb database,there is only one document below:
 {
 "_id" : ObjectId("5226efc5ccf23796f9590f67"),
 "count" : NumberLong(126045),
 "eissn" : "1466-5034",
 "grade" : 0.0,
 "issn" : "1466-5026",
 "journalAlias" : ["International journal of systematic and evolutionary microbiology", "Int J Syst Evol Microbiol", "Int. J. Syst. Evol. Microbiol."],
 "journalName" : "International journal of systematic and evolutionary microbiology",
 "ccNameCount" : [{
 "ccName" : "ATCC",
 "paperCount" : 1,
 "strainCount" : 2
 }, {
 "ccName" : "ATCC",
 "paperCount" : 3,
 "strainCount" : 4
 }, {
 "ccName" : "BCCM/DCG",
 "paperCount" : 5,
 "strainCount" : 6
 }, {
 "ccName" : "BCCM/IHEM",
 "paperCount" : 0,
 "strainCount" : 0
 }
 }

What I want ,is to find out how many different ccNameCout.paperCount whose ccName is "ATCC".So ,in my java code ,I write like this:

coll.distinct("ccNameCount.paperCount",new BasicDBObject("ccNameCount.ccName","ATCC")),

but the result is 4, instead of 2.

I know the process of function distinct.Firstly , it search the collection whose ccNameCount.ccName is "ATCC", and then ,it will do a distinct() in these found documents.

But what I need, is to find how many different paperCount whose ccName is "ATCC". Anyone can help me ?

• joao: Do you need to know the different paperCount numbers or just how many are different?
• joao: Do you still have any problems with this? If my answer solves your problem as you intended mark it as accepted.

Answer 1

I believe the best way to achieve that is by using the aggregation framework:

db.collection.aggregate(
 {$unwind : "$ccNameCount"},
 {$match : {
 "ccNameCount.ccName" : "ATCC"
 }},
 {$group: {
 _id : "$ccNameCount.paperCount"
 }},
 {$group: {
 _id : "count",
 total : {"$sum" : 1}
 }}
);

This will give you the distinct count of paperCount for ccName="ATCC" in the results total field.

Also, If you need to know each of the distinct values besides the total count you can use the $addToSet operator:

db.collection.aggregate(
 {$unwind : "$ccNameCount"},
 {$match : {
 "ccNameCount.ccName" : "ATCC"
 }},
 {$group: {
 _id : "$ccNameCount.paperCount"
 }},
 {$group: {
 _id : "count",
 total : {"$sum" : 1},
 distinctValues : {$addToSet : "$_id"}
 }}
);