Is it possible make an aggregation of multiples collections at once?

Hello everyone

I would like to know if it could be possible to make an aggregation of multiple collections in a single aggregation.

I do not want to use any javascript function to perform this but only a single aggregation call.

Is that possible?

Sincerely
Ezequias

Take a look at

You might also be interested in course M121 from

1 Like

I don’t really want to make a lookup but some summarization steps like $count in multiple collections at once.

Thank you.

Hello, @Ezequias_Rocha!

As @steevej already pointed out, it is possible to do with $lookup operator.

Let me show you by an example.

Assume, we have the following dataset:

db.players.insertMany([
  { player: 'Bob', fromTeam: 'A' },
  { player: 'Sam', fromTeam: 'B' },
  { player: 'Steeve', fromTeam: 'B' },
]);

db.teams.insertMany([
  { team: 'A', country: 'US' },
  { team: 'B', country: 'Canada' },
]);

db.coaches.insertMany([
  { coach: 'Daniel', yearOfExperience: 10 },
]);

To count the documents in each collection and get the result like this:

{ "totalPlayers" : 3, "totalTeams" : 2, "totalCoaches" : 1 }

We can use the following aggregation, that uses a set of $lookup stages with nested pipeline:

db.players.aggregate([
  // count documents in the current collection
  {
    $count: 'totalPlayers',
  },
  // join other collections, in which you need
  // to count documents
  {
    $lookup: {
      from: 'teams',
      pipeline: [
        {
          // count the documents in this specific
          // collection with the $count stage
          $count: 'result',
        },
      ],
      as: 'totalTeams',
    },
  },
  {
    $lookup: {
      from: 'coaches',
      pipeline: [
        {
          $count: 'result',
        },
      ],
      as: 'totalCoaches',
    },
  },
  // $convert arrays, returned by $lookup pipelines,
  // so we count easily reach the 'result' prop
  {
    $unwind: '$totalTeams',
  },
  {
    $unwind: '$totalCoaches',
  },
  // reset the total-props by reaching the 'result' value
  {
    $addFields: {
      totalTeams: '$totalTeams.result',
      totalCoaches: '$totalCoaches.result',
    },
  },
]).pretty();
2 Likes

Thank you @slava in my case i have no match (I would like to perform an full outer join) and would like to filter elements by a common field type (a date type) .

I could apply the same strategy?

BTW: I am in v4.0.6

Best regards
Ezequias

In the example aggregation above we count all the documents from certain collections.
But, you can also selectively filter the documents form each collection. Just add the $match stage before any $count stage, depending what collection you want to filter before count.

It worked! @slava :+1:

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.