CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Eyadkht
Eyadkht

Reputation: 81

MongoDB Combine/Merge the results of two aggregates?

How can I write a query/aggregate that can give me the total number of persons who sent and received a message? For example, A exists in the "from" field, and A also exists in one of the lists of "To" field then it should be counted.

    { "_id" : 1, "from" : "A", "To" : [B,C,F], "message":"lorem ipsum" }
    { "_id" : 2, "from" : "B", "To" : [A] , "message":"lorem ipsum" }
    { "_id" : 3, "from" : "C", "To" : [A,E] , "message":"lorem ipsum"}
    { "_id" : 4, "from" : "D", "To" : [E] , "message":"lorem ipsum"}
    { "_id" : 6, "from" : "A", "To" : [E,B ], "message":"lorem ipsum" }

The total number for this example would be 3 (A, B, C), D is not counted as it only sent a message and did not receive any

I have been able to have a distinct list of "from" field, then I unwind the "To" field and got another list. But now I am stuck on how to combine the two results together.

Upvotes: 1

Views: 71

Answers (1)

matthPen
matthPen

Reputation: 4343

The idea is to group all your docs to get 2 arrays, and get the intersection of this array.

db.collection.aggregate([
  {
    $group: {
      _id: null,
      froms: {
        $addToSet: "$from"
      },
      tos: {
        $push: "$To"
      }
    }
  },
  {
    $addFields: {
      tos: {
        $reduce: {
          input: "$tos",
          initialValue: [],
          in: {
            $setUnion: [
              "$$value",
              "$$this"
            ]
          }
        }
      }
    }
  },
  {
    $addFields: {
      count: {
        $size: {
          $setIntersection: [
            "$froms",
            "$tos"
          ]
        }
      }
    }
  }
])

The first stage aggregate all your docs and create arrays of 'To' and 'From".

The second stage just reduce 'tos' array, as it will be an array of array after the first stage.

The third and last stage get the size of array resulting of your 'tos' and 'froms' arrays intersection.

You can test it here

Upvotes: 1

Related Questions