Aggregate Fields together

Question

I have the following structure as an input from which data needs to be aggregated:

I need to aggregate the data such that I end up with the following structure:

start: A {
    tripdetails: [{
        destination: B [{
            duration: 10,
            type: male
        },
            duration: 12,
            type: female
        },
            duration: 9,
            type: female
        }]
    ]}
}

Basically I need to group "type" and "duration" together under the same destination.

I came up with the following query, but this results in a a single field for "type" for each "destination", but not for every "duration".

db.test.aggregate( 
  { 
    $group: { 
      _id:   {"StationID": "$start", "EndStationID": "$destination"}, 
      durations: {$addToSet: "$duration" },
      usertypes: {$addToSet: "$type" }
    } 
  },
  { 
    $group: {
      _id: "$_id.StationID",
      Tripcount_out: {$sum: "durations"},
      Trips_out: { $addToSet: { EndStationID: "$_id.EndStationID", Tripduration: "$durations", Usertype: "$usertypes"} }
    } 
  }
)

My question is how I can achieve the structure described above.

Answer 1

You could try running the following aggregate pipeline:

db.test.aggregate([
    {
        "$group": {
            "_id": { "StationID": "$start", "EndStationID": "$destination" }, 
            "details": {
                "$push": {
                    "duration": "$duration",
                    "type": "$type"
                }
            }            
        }
    },
    {
        "$group": {
            "_id": "$_id.StationID",
            "tripdetails": {
                "$push": {
                    "destination": "$_id.EndStationID",
                    "trips": "$details"
                }
            }
        }
    }
])

which yields:

{
    "_id" : "A",
    "tripdetails" : [ 
        {
            "destination" : "B",
            "trips" : [ 
                {
                    "duration" : 10,
                    "type" : "male"
                }, 
                {
                    "duration" : 9,
                    "type" : "female"
                }, 
                {
                    "duration" : 12,
                    "type" : "female"
                }
            ]
        }
    ]
}