MongoDB - group composite key with nested fields

Question

I have this document in mongodb:

{ "_id":"26/04/2015 09:50", "reservations":130, "Event_types":[ { "type":"Party", "events":[ { "eventName":"After Party", "total_count":130, "by":[ { "siteName":"club8", "countArray":[ { "bucket":"default", "value":40 } ] }, { "siteName":"PostParty", "countArray":[ { "bucket":"1", "value":70 }, { "bucket":"2", "value":20 } ] } ] } ] } ] } 

What i'm looking for

I want to sum the "value" field and the group by these fields:

• of type
• event_name
• Hostname

So, for the document that I have, I would expect to get:

• For the combination {"Party", "After the party", "club8"} the amount is 40
• For the combination {"Party", "After Party", "PostParty"} the amount of 90

What i tried

I tried using an aggregate statement with a compound key for _id:

 db.testing.aggregate( { $group : { _id : { type:'$Event_types.type', name: '$Event_types.events.eventName', siteName: '$Event_types.events.by.siteName' } , total : { $sum : '$Event_types.events.by.countArray.value' } } }); 

results

one document with three arrays - one for each value that I want to group. The array "siteName" contains 2 values ​​available for "siteName". The “sum” does not seem to sum anything, and it appears only once — I expected to see it next to each “SiteName” value in the document.

  { "_id":{ "type":[ "Party" ], "name":[ [ "After Party" ] ], "siteName":[ [ [ "club8", "PostParty" ] ] ] }, "total":0 } 

Am I using "aggregate" in the wrong way, or is the scheme I'm using not suitable for my purpose? Thanks.

Answer 1

You need to first apply the $unwind operator on all arrays so that you can aggregate the calculation using the $group operator later in the pipeline steps. As a result, you will get the aggregation pipeline as follows:

 db.testing.aggregate([ { "$unwind": "$Event_types" }, { "$unwind": "$Event_types.events" }, { "$unwind": "$Event_types.events.by" }, { "$unwind": "$Event_types.events.by.countArray" }, { "$group": { "_id": { "type": "$Event_types.type", "name": "$Event_types.events.eventName", "siteName": "$Event_types.events.by.siteName" }, "total": { "$sum": "$Event_types.events.by.countArray.value" } } }, { "$project": { "_id": 0, "type": "$_id.type", "name": "$_id.name", "siteName": "$_id.siteName", "total": 1 } } ]); 

Output

 /* 1 */ { "result" : [ { "total" : 90, "type" : "Party", "name" : "After Party", "siteName" : "PostParty" }, { "total" : 40, "type" : "Party", "name" : "After Party", "siteName" : "club8" } ], "ok" : 1 }