MongoDB aggregation on multiple collections

Question

I need to create aggregation that runs on multiple collections with similar structure. I know about the $lookup aggregation, but i actually do not want to join between the documents, but to make a list of all the documents from all the collections. To better clarify my intention, I'll use an example.

Students collection:

{
     "_id" : ObjectId("57278a449fb5ba91248b3bc0"),
     "age": 22
}

Teachers collection:

{
     "_id" : ObjectId("57278a449fb5ba91248b3bc0"),
     "age": 28
}

I want to create an aggregation that will give me the average age of both of the collections together. How can i do it without using two aggregations and combine the results with my code?

Answer 1

You can get away with using new pipeline style lookups like this:

db.getCollection('students').aggregate(
    [
        {
            $group: {
                '_id': 0
            }
        },
        {
            $lookup: {
                from: 'students',
                let: {},
                pipeline: [
                    { $group: {
                        '_id': 0,
                        'avg': { $avg: '$age' },
                        'count': { $sum: 1 }
                    } }
                ],
                as: 'students'
            }
        },
        {
            $lookup: {
                from: 'teachers',
                let: {},
                pipeline: [
                    { $group: {
                        '_id': 0,
                        'avg': { $avg: '$age' },
                        'count': { $sum: 1 }
                    } }
                ],
                as: 'teachers'
            }
        },
        {
            $unwind: {
                path : '$students',
            }
        },
        {
            $unwind: {
                path : '$teachers',
            }
        },
        {
            $project: {
                'avg_age': { $divide: [
                    { $sum: [
                        { $multiply: [ '$students.avg', '$students.count' ] },
                        { $multiply: [ '$teachers.avg', '$teachers.count' ] }
                    ] },
                    { $sum: [ '$students.count', '$teachers.count' ] },
                ] }
            }
        },
    ]
);

The first $group stage gives you single empty document to start from, so each $lookup is only executed once. You can combine the averages from each collection by weighting by the count, this gives the same result as if taking the average over all the documents.