MongoDB: how to aggregate from multiple collections with same aggregation pipeline

Question

I'm trying to get aggregations with same aggregation pipeline including $match and $group operations from multiple collections.

For example,

with a users collection and collections of questions, answers and comments where every document has authorId and created_at field,

db = [
    'users': [{ _id: 123 }, { _id: 456} ],
    'questions': [
        { authorId: ObjectId('123'), createdAt: ISODate('2022-09-01T00:00:00Z') },
        { authorId: ObjectId('456'), createdAt: ISODate('2022-09-05T00:00:00Z') },
    ],

    'answers': [
        { authorId: ObjectId('123'), createdAt: ISODate('2022-09-05T08:00:00Z') },
        { authorId: ObjectId('456'), createdAt: ISODate('2022-09-01T08:00:00Z') },
    ],
    'comments': [
        { authorId: ObjectId('123'), createdAt: ISODate('2022-09-01T16:00:00Z') },
        { authorId: ObjectId('456'), createdAt: ISODate('2022-09-05T16:00:00Z') },
    ],
]

I want to get counts of documents from each collections with created_at between a given range and grouped by authorId. A desired aggregation result may look like below. The _ids here are ObjectIds of documents in users collection.

\\ match: { createdAt: { $gt: ISODate('2022-09-03T00:00:00Z) } }
[
    { _id: ObjectId('123'), questionCount: 0, answerCount: 1, commentCount: 0 }, 
    { _id: ObjectId('456'), questionCount: 1, answerCount: 0, commentCount: 1 }
]

Currently, I am running aggregation below for each collection, combining the results in the backend service. (I am using Spring Data MongoDB Reactive.) This seems very inefficient.

db.collection.aggregate([
    { $match: { 
        created_at: { $gt: ISODate('2022-09-03T00:00:00Z') }
    }},
    { $group : {
        _id: '$authorId',
        count: {$sum: 1}
    }}
])

How can I get the desired result with one aggregation?

I thought $unionWith or $lookup may help but I'm stuck here.

Charchit Kapoor · Accepted Answer · 2022-09-23T08:22:36.647

You can try something like this, using $lookup, here we join users, with all the three collections one-by-one, and then calculate the count:

db.users.aggregate([
  {
    "$lookup": {
      "from": "questions",
      "let": {
        id: "$_id"
      },
      "pipeline": [
        {
          "$match": {
            $expr: {
              "$and": [
                {
                  "$gt": [
                    "$createdAt",
                    ISODate("2022-09-03T00:00:00Z")
                  ]
                },
                {
                  "$eq": [
                    "$$id",
                    "$authorId"
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "questions"
    }
  },
  {
    "$lookup": {
      "from": "answers",
      "let": {
        id: "$_id"
      },
      "pipeline": [
        {
          "$match": {
            $expr: {
              "$and": [
                {
                  "$gt": [
                    "$createdAt",
                    ISODate("2022-09-03T00:00:00Z")
                  ]
                },
                {
                  "$eq": [
                    "$$id",
                    "$authorId"
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "answers"
    }
  },
  {
    "$lookup": {
      "from": "comments",
      "let": {
        id: "$_id"
      },
      "pipeline": [
        {
          "$match": {
            $expr: {
              "$and": [
                {
                  "$gt": [
                    "$createdAt",
                    ISODate("2022-09-03T00:00:00Z")
                  ]
                },
                {
                  "$eq": [
                    "$$id",
                    "$authorId"
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "comments"
    }
  },
  {
    "$project": {
      "questionCount": {
        "$size": "$questions"
      },
      "answersCount": {
        "$size": "$answers"
      },
      "commentsCount": {
        "$size": "$comments"
      }
    }
  }
])

Playground link. In the above query, we use pipelined form of $lookup, to perform join on some custom logic. Learn more about $lookup here.

Another way is this, perform normal lookup and then filter out the elements:

db.users.aggregate([
  {
    "$lookup": {
      "from": "questions",
      "localField": "_id",
      "foreignField": "authorId",
      "as": "questions"
    }
  },
  {
    "$lookup": {
      "from": "answers",
      "localField": "_id",
      "foreignField": "authorId",
      "as": "answers"
    }
  },
  {
    "$lookup": {
      "from": "comments",
      "localField": "_id",
      "foreignField": "authorId",
      "as": "comments"
    }
  },
  {
    "$project": {
      questionCount: {
        "$size": {
          "$filter": {
            "input": "$questions",
            "as": "item",
            "cond": {
              "$gt": [
                "$$item.createdAt",
                ISODate("2022-09-03T00:00:00Z")
              ]
            }
          }
        }
      },
      answerCount: {
        "$size": {
          "$filter": {
            "input": "$answers",
            "as": "item",
            "cond": {
              "$gt": [
                "$$item.createdAt",
                ISODate("2022-09-03T00:00:00Z")
              ]
            }
          }
        }
      },
      commentsCount: {
        "$size": {
          "$filter": {
            "input": "$comments",
            "as": "item",
            "cond": {
              "$gt": [
                "$$item.createdAt",
                ISODate("2022-09-03T00:00:00Z")
              ]
            }
          }
        }
      }
    }
  }
])

Playground link.

Thanks for the answer, but where should I put `$match`(or any filtering operation for `created_at`) in the pipeline? — grus, Sep 22 '22 at 10:47
It should be the first stage, before `$lookup`. Updated the answer, as well. — Charchit Kapoor, Sep 22 '22 at 10:49
Can I put filter before joining documents with `$lookup`? As I mentioned in the question, I am trying to filter documents in other collections than `users`. — grus, Sep 22 '22 at 10:54
ok, that can be done, within $lookup only. Can you add some sample documents from all collections to the question itself? I will update the query accordingly. — Charchit Kapoor, Sep 22 '22 at 10:57
Thanks a lot, however it seems Spring Data MongoDB doesn't support $lookup with let/pipeline. Is there any other query for the same result? (I am currently using custom AggregationOperation for this query) — grus, Sep 23 '22 at 08:15
Yess there is a way, I think. It will be a bit tedious. Let me create a query for it. — Charchit Kapoor, Sep 23 '22 at 08:17

MongoDB: how to aggregate from multiple collections with same aggregation pipeline

1 Answers1