Mongodb distinct() in an array

arraymongodb

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 ?

Best Answer

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"}
    }}
);
Related Question