MongoDB aggregation '$sortBy' using combination of fields

Question

I have a collection with fields sender and receiver and I want to perform a group by aggregation on the collection with the fields sender and receiver.

Example

collection:

[
  { "id": 1, "sender": "A", "receiver": "B"},
  { "id": 3, "sender": "B", "receiver": "A"},
  { "id": 4, "sender": "B", "receiver": "C"},
  { "id": 5, "sender": "C", "receiver": "A"}
]

Expected aggregated output will be:

[
  { "_id": { "user1": "A", "user2": "B"}, "total": 2 },
  { "_id": { "user1": "A", "user2": "C"}, "total": 1 },
  { "_id": { "user1": "B", "user2": "C"}, "total": 1 },
]

Is this possible by using MongoDB aggregation?

Answer 1

1. $addFields: Add users field with an array of sender and receiver.
2. $unwind: Deconstruct array field users for sorting in Step 3.
3. $sort: Sort deconstructed users array from Step 2 in alphabetical order.
4. $group: Group by id to construct users to the array.
5. $group: Group by user1 and user2 for total with $count.
6. $sort: Sort _id.user1 and _id.user2 in alphabetical order.

db.collection.aggregate([
  {
    $addFields: {
      "users": [
        "$sender",
        "$receiver"
      ]
    }
  },
  {
    $unwind: "$users"
  },
  {
    $sort: {
      "users": 1
    }
  },
  {
    $group: {
      "_id": "$id",
      "users": {
        $push: "$users"
      }
    }
  },
  {
    $group: {
      "_id": {
        "user1": {
          $arrayElemAt: [
            "$users",
            0
          ]
        },
        "user2": {
          $arrayElemAt: [
            "$users",
            1
          ]
        }
      },
      "total": {
        $count: {}
      }
    }
  },
  {
    $sort: {
      "_id.user1": 1,
      "_id.user2": 1
    }
  }
])

Output

[
  {
    "_id": {
      "user1": "A",
      "user2": "B"
    },
    "total": 2
  },
  {
    "_id": {
      "user1": "A",
      "user2": "C"
    },
    "total": 1
  },
  {
    "_id": {
      "user1": "B",
      "user2": "C"
    },
    "total": 1
  }
]

Sample Mongo Playground