multiple group by query in mongodb aggregate

Question

This is the structure of my document

{
"_id" : "8113593870",
"_class" : "com.loylty.messagingEngine.entities.message.GroupIdDeliveryStatusReport",
"DATA_LIST" : [ 
    {
        "_id" : "8113593870-1",
        "mobile" : "7874671667",
        "status" : "DELIVRD",
        "senttime" : "2018-01-30 13:29:40",
        "dlrtime" : "2018-01-30 13:29:43",
        "custom" : "7874671667"
    }, 
    {
        "_id" : "8113593870-2",
        "mobile" : "7507829969",
        "status" : "DNDNUMB",
        "senttime" : "2018-01-30 13:29:40",
        "dlrtime" : "2018-01-30 13:29:40",
        "custom" : "7507829969"
    }
],
"CAMPAIGN_ID" : "5a70252612d91c7b085df083",
"MESSAGE_CONFIG_ID" : "5a702570f9dede4a357ffac4"

}

There will be multiple documents of this structure

I want to do like this

SQL: SELECT MESSAGE_CONFIG_ID,count(*) from GroupIdDeliveryStatusReport where CAMPAIGN_ID = '5a70252612d91c7b085df083' group BY DATA_LIST.mobile, MESSAGE_CONFIG_ID

How to do the same in mongo shell and spring-data .

I want the count of unique mobile number in DATA_LIST of each unique MESSAGE_CONFIG_ID

This is something I tried But I am not getting as expected .

db.getCollection('GROUP_ID_DELIVERY_STATUS').aggregate(
     {
        "$match" : {"CAMPAIGN_ID": "5a70252612d91c7b085df083" }
    },   
    { "$unwind" : "$DATA_LIST"},
    {
        "$match" : {"DATA_LIST.status": "DELIVRD" }
    },
    {
        $group: { _id: { mobile: '$DATA_LIST.mobile', MESSAGE_CONFIG_ID: '$MESSAGE_CONFIG_ID'},count: { $sum: 1 }  }

    }  

)

For spring-data I tried this

Aggregation aggregation = newAggregation(
            match(Criteria.where("CAMPAIGN_ID").in(campaignId)),
            unwind("DATA_LIST"),
            match(Criteria.where("DATA_LIST.status").is("DELIVRD")),
            group("DATA_LIST.mobile","MESSAGE_CONFIG_ID"),
            project("MESSAGE_CONFIG_ID","DATA_LIST.mobile")
    );

Answer 1

You can try below aggregation.

First $group to output the distinct values followed by second $group to count mobile numbers for each "MESSAGE_CONFIG_ID".

db.GROUP_ID_DELIVERY_STATUS.aggregate([
  {"$match":{"CAMPAIGN_ID":"5a70252612d91c7b085df083"}},
  {"$unwind":"$DATA_LIST"},
  {"$match" : {"DATA_LIST.status": "DELIVRD"}},
  {"$group":{
    "_id":{
      "mobile":"$DATA_LIST.mobile",
      "MESSAGE_CONFIG_ID":"$MESSAGE_CONFIG_ID"
    }
  }},
  {"$group":{
    "_id":"$_id.MESSAGE_CONFIG_ID",
    "mobilecount":{"$sum":1}
  }}
])