Mongo aggregate group by pair-CodePudding

I have collection like this

User	Receiver	IsRead
A	B	false
B	A	false
A	B	false
A	C	false
C	A	false

How can I group it by both User and Receiver to get result like this

Combined Field	Messages
AB OR BA	3
AC OR CA	2

tried this

$group: {
    _id: {$or: [{userId: "$recipientId", recipientId: "$userId"}, {userId: "$userId", recipientId: "$recipientId"}]},
    messages: {$sum: {$cond: [{$eq: ['$isRead', false]}, 1, 0]}} // count
}

CodePudding user response：

You can set the $group key to be $setUnion of the User and Receiver field.

db.collection.aggregate([
  {
    $group: {
      _id: {
        "$setUnion": [
          [
            "$User"
          ],
          [
            "$Receiver"
          ]
        ]
      },
      "Messages": {
        $sum: {
          "$cond": {
            "if": {
              $not: "$IsRead"
            },
            "then": 1,
            "else": 0
          }
        }
      }
    }
  }
])

Here is the Mongo playground for your reference.