Mongodb aggregate for nested subdocument field

Question

I'm trying to get the sum of "Duration" from mongo subdocument's using aggregate, Here I have event_id = "5656b3655b9e5c8812000007", I need to get the sum of "Duration" field by matching the event_id = "5656b3655b9e5c8812000007" in above example.

Example:

"history" : [
        {
            "timestamp" : "2015-10-26 14:39:15", 
            "event" : "Creation", 
            "createdby" : "Ws", 
            "data" : [
                {
                    "crm_base_contact_id" : "1847", 
                    "crm_imported_files_id" : ""
                }
            ]
        }, 
        {
            "timestamp" : "2015-10-26 15:12:28", 
            "event" : "Task", 
            "createdby" : "Auto-Filter", 
            "data" : [
                {
                    "Campaign ID" : "219", 
                    "Campaign Name" : "ASA", 
                    "Task ID" : "2541639", 
                    "Filter Name" : "ASA", 
                    "Filter ID" : "826"
                }
            ]
        }, 
        {
            "event_id" : ObjectId("5656b3655b9e5c8812000007"), 
            "timestamp" : "2015-11-26 08:23:17", 
            "event" : "Session", 
            "createdby" : "ABC", 
            "data" : [
                {
                    "Campaign ID" : "219", 
                    "Task ID" : "2541639", 
                    "viopCalls" : [
                        {
                            "timestamp" : "2015-11-26 08:25:42", 
                            "CallID" : "46", 
                            "Duration" : NumberInt(5), 
                            "TelNumber" : "0685356189"
                        }, 
                        {
                            "timestamp" : "2015-11-26 08:26:13", 
                            "CallID" : "45", 
                            "Duration" : NumberInt(3), 
                            "TelNumber" : "0685356189"
                        }
                    ]
                }
            ]             
        }
    ], 


Expected output : Duration : 8

Please can anyone help me to get the sum of duration column !!!

is history a key in collection?

Sarath Nair
– Sarath Nair

2015-11-26 09:39:54 +00:00
Commented Nov 26, 2015 at 9:39 — Sarath Nair
– Sarath Nair, Commented Nov 26, 2015 at 9:39
yes its a key in collection

US-1234
– US-1234

2015-11-26 09:40:50 +00:00
Commented Nov 26, 2015 at 9:40 — US-1234
– US-1234, Commented Nov 26, 2015 at 9:40

Sarath Nair · Accepted Answer · 2015-11-26 09:47:00Z

1

Use can use the following aggregate query:

db.collection.aggregate(
  {$unwind: '$history'},
  {$match: {'history.event_id': ObjectId("5656b3655b9e5c8812000007")}},
  {$unwind: '$history.data'},
  {$unwind: '$history.data.viopCalls'},
  {$group: {_id: null, duration: {$sum: '$history.data.viopCalls.Duration'}}}
)

Result:

{ "_id" : null, "duration" : 8 }

answered Nov 26, 2015 at 9:47

Sarath Nair

2,8682 gold badges24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mongodb aggregate for nested subdocument field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related