Conditionally group and sum of the elements from an array

Question

I want to group by APPName and I want find how many PrestoBarImpression, PrestoKeyCountChange, PrestoTileImpression for every application for a particular day (just the sum of order counts).

This is so I can generate a report with this information. I need how many order counts of PrestoTileImpression, how many order counts of PrestoBarImpression, how many order counts of PrestoTileClick for every application.

The below is my Document.

{
    "ClientId": "XYZ123",
    "location": {
    "Name": "Hyderabad",
    "Country": "India",
    "Zip": "500084",
    "Gps": {
        "lat": "17.463607",
        "lon": "78.344279"
    }
    },
    "Network": {
    "Operator": "Airtel",
    "Type": "wifi",
    "TowerID": "123",
    "IP": "1.1.1.1"
    },
    "SessionTimeStamp": {
    "Start": ISODate("2015-06-02T05:36:49.045        Z"),
    "End": ISODate("2015-06-02T05:36:56.045        Z"),
    "Duration": "7000"
    },
    "AppName": "WhatsApp",
    "Text": "Key1 Key2 Key3 Key4",
    "Actions": [{
    "Type": "PrestoBarImpression",
    "CampaignId": 1,
    "keyword": "key1",
    "prestoCount": 1,
    "duration": 100,
    "OrderCount": 1
    }, {
    "Type": "PrestoKeyCountChange",
    "CampaignId": 1,
    "keyword": "key1",
    "prestoCount": 1,
    "OrderCount": 2
    }, {
    "Type": "PrestoBarImpression",
    "CampaignId": 2,
    "keyword": "key2",
    "prestoCount": 2,
    "duration": 150,
    "OrderCount": 3
    }, {
    "Type": "PrestoKeyCountChange",
    "CampaignId": "2",
    "keyword": "key2",
    "prestoCount": 2,
    "OrderCount": 4
    }, {
    "Type": "PrestoBarImpression",
    "CampaignId": 1,
    "keyword": "key3",
    "prestoCount": 2,
    "duration": 200,
    "OrderCount": 5
    }, {
    "Type": "PrestoTileImpression",
    "CampaignId": 1,
    "duration": 200,
    "OrderCount": 6
    }, {
    "Type": "PrestoTileImpression",
    "AdditionalAction": "swipeRight",
    "CampaignId": 2,
    "duration": 200,
    "OrderCount": 7
    }, {
    "Type": "PrestoTileClick",
    "AdditionalAction": "swipeRight",
    "CampaignId": 2,
    "OrderCount": 8
    }, {
    "Type": "PrestoBarImpression",
    "CampaignId": 2,
    "keyword": "key4",
    "prestoCount": 2,
    "duration": 150,
    "OrderCount": 9
    }]
}

I got the below output by using @Viswas response I made a query.

Query

[  
{  
    "$match":{  
        "SessionTimeStamp.Start":{  
            "$gte":                ISODate("2015-06-01T18:30:00.000                Z"),
            "$lte":                ISODate("2015-06-04T18:29:59.000                Z")
        }
    }
},
{  
    "$unwind":"$Actions"
},
{  
    "$match":{  
        "Actions.Type":{  
            "$in":[  
                "PrestoBarImpression",
                "PrestoKeyCountChange",
                "PrestoTileImpression"
            ]
        }
    }
},
{  
    "$group":{  
        "_id":{  
            "AppName":"$AppName",
            "type":"$Actions.Type"
        },
        "total":{  
            "$sum":"$Actions.OrderCount"
        }
    }
},
{  
    "$sort":{  
        "total":1,

    }
}

]

Output

{  
"result":[  
    {  
        "_id":{  
            "AppName":"WhatsApp",
            "type":"PrestoKeyCountChange"
        },
        "total":6
    },
    {  
        "_id":{  
            "AppName":"hike",
            "type":"PrestoKeyCountChange"
        },
        "total":6
    },
    {  
        "_id":{  
            "AppName":"hike",
            "type":"PrestoTileImpression"
        },
        "total":13
    },
    {  
        "_id":{  
            "AppName":"WhatsApp",
            "type":"PrestoTileImpression"
        },
        "total":13
    },
    {  
        "_id":{  
            "AppName":"hike",
            "type":"PrestoBarImpression"
        },
        "total":18
    },
    {  
        "_id":{  
            "AppName":"WhatsApp",
            "type":"PrestoBarImpression"
        },
        "total":18
    }
],
"ok":1.0000000000000000

}

I need the output in below format

[  
{  
    "AppName":"WhatsApp",
    " PrestoTileImpression":13,
    "PrestoKeyCountChange":6,
    "PrestoBarImpression":18,
    "count":"10 (This is how many times thee Application presents in document, because I need to find top 10 apps Need to sort the output by this count)"
},
{  
    "AppName":"Hike",
    " PrestoTileImpression":13,
    "PrestoKeyCountChange":6,
    "PrestoBarImpression":18,
    "count":"10 "
}

]

possible duplicate of [group by dates in mongodb](http://stackoverflow.com/questions/5168904/group-by-dates-in-mongodb) — Philipp, Jun 18 '15 at 11:15
Also, please try to come up with a more descriptive question title. Questions should be titled in a way that someone who has the same problem can find it. — Philipp, Jun 18 '15 at 11:21

score 1 · Answer 1 · 2015-06-18T12:50:28.637

It's really all about filtering the array content to get just the items you want in the sum:

db.collection.aggregate([
    // Filter documents with matching entries first
    { "$match": {
        "Actions.Type": { "$in": [
            "PrestoBarImpression",
            "PrestoKeyCountChange",
            "PrestoTileImpression"
        ]}
    }},
    // Unwind the array entries
    { "$unwind": "$Actions" },
    // Filter to only keep desired array entries
    { "$match": {
        "Actions.Type": { "$in": [
            "PrestoBarImpression",
            "PrestoKeyCountChange",
            "PrestoTileImpression"
        ]}
    }},
    // Group by AppName and current day (finishing)
    { "$group": {
        "_id": {
            "AppName": "$AppName",
            "day": {
                "year": { "$year": "$SessionTimeStamp.End" },
                "month": { "$month": "$SessionTimeStamp.End" },
                "day": { "$dayOfMonth": "$SessionTimeStamp.End" }
            },
            "type": "$Actions.Type"
        },
        "total": { "$sum": "$Actions.OrderCount" }
    }},
    // Sort as however you require
    { "$sort": {
        "_id.AppName": 1,
        "_id.day": 1,
        "_id.type": 1,
        "total": -1
    }}
])

Or if you want all those fields per document then right after the existing group add:

{ "$group": {
    "_id": {
        "AppName": "$_id.AppName",
        "day": "$_id.day",
     },
     "PrestoBarImpression": { "$sum": {
         "$cond": [
             { "$eq": [ "$_id.type", "PrestoBarImpression" ] },
             "$total",
             0
         ]
     }},
     "PrestoKeyCountChange": { "$sum": {
         "$cond": [
             { "$eq": [ "$_id.type", "PrestoKeyCountChange" ] },
             "$total",
             0
         ]
     }},
     "PrestoTileImpression": { "$sum": {
         "$cond": [
             { "$eq": [ "$_id.type", "PrestoTileImpression" ] },
             "$total",
             0
         ]
     }}
}}

Which tallies the totals per field into single documents for "AppName" and "day".

You probably want to add a "date range" match to that first $match pipeline rather than add up everything in the collection and just do so between dates as well.

Thanks for answer, However I need the PrestoBarImpression, PrestoTileImpression..etc ordercounts independently — user1920, Jun 18 '15 at 12:38
@kirankumarreddy No problem. It's a simple change and already done. You really need to be "very clear" in your questions and preferably show some expected output as well as your input document. — , Jun 18 '15 at 12:42

score 0 · Accepted Answer · edited May 23 '17 at 12:29

You should use aggregation to get result.

If you want ActionType wise OrderCount for given date (particular) date then you need to first match start to your date and then group data according to Action.Type. The query will be as following:

db.collection.aggregate({
    $match: {
    "SessionTimeStamp.Start": ISODate("2015-06-02T05:36:49.045Z")
    }
}, {
    $group: {
    "_id": "AppName",
    "Document": {
        $push: {
            "SessionTimeStamp": "$SessionTimeStamp",
            "Actions": "$Actions",
            "AppName": "$AppName"
        }
    }
    }
}, {
    $unwind: "$Document"
}, {
    $unwind: "$Document.Actions"
}, {
    $group: {
    _id: "$Document.Actions.Type",
    "OrderCount": {
        $sum: "$Document.Actions.OrderCount"
    },
    "App": {
        $first: "$Document.AppName"
    }
    }
}, {
    $project: {
    "_id": 0,
    "OrderCount": 1,
    "ActionType": "$_id",
    "App": 1
    }
})

Edit after comment of question author:

Reference to Duplicate Question by author

Please verify spelling of count for appNames as it is different (count, Count) for some appNames

You should use following query-

db.collection.aggregate({
    $match: {
    "SessionTimeStamp.Start": {
        $gte: ISODate("2015-06-02T05:36:49.045Z")
    },
    "SessionTimeStamp.End": {
        $lte: ISODate("2015-06-02T05:36:56.045Z")
    }
    }
}, {
    $unwind: "$Actions"
}, {
    $group: {
    "_id": {
        "AppName": "$AppName",
        "Type": "$Actions.Type"
    },
    "count": {
        "$sum": "$Actions.Count"
    },
    "appCount": {
        $sum: 1
    }
    }
}, {
    $project: {
    "AppName": "$_id.AppName",
    "Type": "$_id.Type",
    "count": 1,
    "appCount": 1,
    "_id": 0
    }
})

If you still want to assign dynamic values as keys then you can iterate over the cursor you get like -

db.collection.aggregate({$match:{"SessionTimeStamp.Start":{$gte:ISODate("2015-06-02T05:36:49.045Z")},
"SessionTimeStamp.End":{$lte:ISODate("2015-06-02T05:36:56.045Z")}}},
{$unwind:"$Actions"},{$group:{"_id":{"AppName":"$AppName","Type":"$Actions.Type"},
"count":{"$sum":"$Actions.Count"},"appCount":{$sum:1}}},
{$project:{"AppName":"$_id.AppName","Type":"$_id.Type","count":1,
"appCount":1,"_id":0}}).forEach( function(myDoc){  var feeType = {}; 
feeType["AppName"] = myDoc.AppName; feeType[myDoc.Type]= myDoc.count;
feeType["appCount"] = myDoc.appCount; printjson (feeType);})

Conditionally group and sum of the elements from an array

2 Answers2