MongoDB aggregation - group in 2 ways in a single query

Admin 1
Viewed 55

I have a collection of emails:

{ "sender" : "bob@gmail.com", "to" : [ "harry@gmail.com", "tom@gmail.com" ], "text" : "Lorem ipsum dolor sit amet, consectetur adipiscing elit." }
{ "sender" : "bob@gmail.com", "to" : [ "tom@gmail.com" ], "text" : "Lorem ipsum dolor sit amet, consectetur adipiscing elit." }
{ "sender" : "harry@gmail.com", "to" : [ "bob@gmail.com" ], "text" : "Lorem ipsum dolor sit amet, consectetur adipiscing elit." }

I can get the total number of emails sent by each person like this:

db.emails.aggregate({$group: {_id: "$sender", totalSent: {$sum: 1}}})

And total received like this:

db.emails.aggregate({$unwind: "$to"}, {$group: {_id: "$to", totalReceived: {$sum: 1}}})

But how do I do these together in a single query so I get this output:

{ "_id" : "tom@gmail.com", "totalReceived" : 2 }
{ "_id" : "harry@gmail.com", "totalReceived" : 1, totalSent: 1 }
{ "_id" : "bob@gmail.com", "totalReceived" : 1, totalSent: 2 }
aggregation-framework mongodb
1 Answers

There will be some better option to do this, i am sharing one option it might be heavy transaction in process more documents,

  • $facet to get separate your both arrays sent and received,
  • $project to concat both arrays in all using $concatArrays
  • $unwind deconstruct all array
  • $group by all._id means email and get send and received count
db.collection.aggregate([
  {
    $facet: {
      totalSent: [
        {
          $group: {
            _id: "$sender",
            totalSent: { $sum: 1 }
          }
        }
      ],
      totalReceived: [
        { $unwind: "$to" },
        {
          $group: {
            _id: "$to",
            totalReceived: { $sum: 1 }
          }
        }
      ]
    }
  },
  {
    $project: { all: { $concatArrays: ["$totalSent", "$totalReceived"] } }
  },
  { $unwind: "$all" },
  {
    $group: {
      _id: "$all._id",
      totalSent: { $sum: "$all.totalSent" },
      totalReceived: { $sum: "$all.totalReceived" }
    }
  }
])

Playground

user_89871280
Related