title: Mongo Aggregation, group by same field in 2 different arrays
body: <p>I would like to calculate the total commission earned per month per agent with the following dataset:</p>
  
  <pre><code>db.comm.find()
  
  /* 0 */
  { 
  month: 1,
  deals: [
          { agent: { _id: 1, name: 'Joe' }, deal: { _id: 1, comm: 10000 } },
          { agent: { _id: 1, name: 'Joe' }, deal: { _id: 2, comm: 13000 } },
          { agent: { _id: 2, name: 'Sue' }, deal: { _id: 3, comm: 20000 } }
  ],
      referrals: [
          { agent: { _id: 1, name: 'Joe' }, referral_comm: 3000 },
          { agent: { _id: 3, name: 'Pete' }, referral_comm: 2500, other_comm: 1000 }
      ]
  }
  
  /* 1 */
  { 
  month: 2,
  deals: [
          { agent: { _id: 1, name: 'Joe' }, deal: { _id: 4, comm: 11000 } },
          { agent: { _id: 3, name: 'Pete' }, deal: { _id: 5, comm: 21000 } }
  ],
      referrals: [
          { agent: { _id: 2, name: 'Sue' }, referral_comm: 2100, other_comm: 1100 },
          { agent: { _id: 4, name: 'Judy' }, referral_comm: 1100 }
      ]
  }
  </code></pre>
  
  <p>I'm getting incorrect results with the following pipeline:</p>
  
  <pre><code>db.comm.aggregate([
      {
          $unwind: "$deals"
      },
      {
          $project: {
              month: 1,
              agent: "$deals.agent",
              comm: "$deals.deal.comm",
              referrals: 1
          }
      },
      {
          $unwind: "$referrals"
      },
      {
          $project: {
              month: 1,
              agent: 1,
              comm: 1,
              referral_comm: {
                  $add: [
                      "$referrals.referral_comm",
                      "$referrals.other_comm"
                  ]
              }
          }
      },
      {
          $project: {
              month: 1,
              agent: 1,
              comm: {
                  $add: [ "$comm", "$referral_comm" ]
              }
          }
      },
      { 
          $group: {
              _id: {
                  month: "$month",
                  agent: "$agent"
              },
              total: {
                  $sum: "$comm"
              }
          }
      }
  ])
  </code></pre>
  
  <p>The results are:</p>
  
  <pre><code>/* 0 */
  {
      "result" : [ 
          {
              "_id" : {
                  "month" : 1,
                  "agent" : {
                      "_id" : 2,
                      "name" : "Sue"
                  }
              },
              "total" : 23500 //expected 20000
          }, 
          {
              "_id" : {
                  "month" : 1,
                  "agent" : {
                      "_id" : 1,
                      "name" : "Joe"
                  }
              },
              "total" : 30000 //expected 26000
          }, //missing Pete in Month 2
          {
              "_id" : {
                  "month" : 2,
                  "agent" : {
                      "_id" : 3,
                      "name" : "Pete"
                  }
              },
              "total" : 24200 //expected 21000
          }, 
          {
              "_id" : {
                  "month" : 2,
                  "agent" : {
                      "_id" : 1,
                      "name" : "Joe"
                  }
              },
              "total" : 14200 //expected 11000
          }
      ], //missing Sue and Judy
      "ok" : 1
  }
  </code></pre>
  
  <p>My problem is that I don't seem to find the right combination in the pipeline to combine the deals and referrals so that commissions stay with the same agent. Can this be done with the aggregation framework in Mongo, or is the only way to use MapReduce?</p>