How to combine results of two MongoDB aggregation pipeline queries and perform another aggregation query on the combined result without using $facet?

user_7768287 0
Viewed 3635

My first query returns the following result, after various aggregation pipeline stages:

{ 
    "group" : "A",
    "count" : 6, 
    "total" : 20
},
{
    "group" : "B",
    "count" : 2,
    "total" : 50
}

My second query returns the following result, after various aggregation pipeline stages:

{
    "group": "A",
    "count": 4,
    "total": 80
},
{
    "group": "C",
    "count": 12,
    "total": 60
}

Both the queries are performed on the same collection, but groups and transforms the data differently based on the pipeline stages.

Both of my queries use different $match conditions, have various pipeline stages including $facet,$unwind,$group,$project and operators like $map,$reduce,$zip,$subtract...

db.collection.aggregate([
{ $unwind...},
{ $match....},
{ $facet...},
...
])

When I use $facet to run my queries as parallel queries, it gives the following error (because I'm already using $facet in my existing queries) :

$facet is not allowed to be used within a $facet stage

Expected Output:

I need to find the average value for each of the group.

For that, I need to combine the results of both the queries and perform queries on the combined result.

My combined stage should look like this:

{ 
    "group" : "A",
    "count" : 10, 
    "total" : 100 
},
{
    "group" : "B",
    "count" : 2,
    "total" : 50
},
{
    "group": "C",
    "count": 12,
    "total": 60
}

Expected final result with average value for each group:

{
    "group" : "A",
     "avg" : 10 
},
{
    "group" : "B",
    "avg" : 25
},
{
    "group": "C",
    "avg": 5
}

Are there any operators available in MongoDB aggregation pipeline to achieve this without modifying my existing queries?

How to achieve this use case?

Thanks!

aggregation-framework mongodb-query mongodb aggregation merge
2 Answers

You can run your queries separately using $facet and then use below transformation to $group combined results by group and calculate the average:

db.collection.aggregate([
    {
        $facet: {
            first: [ { $match: { "_": true } } ], // your first query
            second: [ { $match: { "_": false } } ], // your second query
        }
    },
    {
        $project: {
            all: {
                $concatArrays: [ "$first", "$second" ]
            }
        }
    },
    {
        $unwind: "$all"
    },
    {
        $group: {
            _id: "$all.group",
            count: { $sum: "$all.count" },
            total: { $sum: "$all.total" },
        }
    },
    {
        $project: {
            _id: 0,
            group: "$_id",
            count: 1,
            total: 1,
            avg: { $divide: [ "$total", "$count" ] }
        }
    }
])

Mongo Playground

Note: I'm using the _ character to indicate which query the document comes from. Obviously you don't need it and you can replace $facet queries with your own

user_62389770

There are different approaches to combine results including $merge,which was introduced in 4.2

But I used the following approach since I use version 4.0

Save both of the aggregation query result in a variable and then insert it into a new temporary collection:

var result  = db.collection.aggregate(...); //query1 here
db.temp.insert(result.toArray());

var result  = db.collection.aggregate(...); //query2 here
db.temp.insert(result.toArray());

// Find out average
db.temp.aggregate([
    {
        $group: {
            _id: "$group",
            count: { $sum: "$count" },
            total: { $sum: "$total" }
        }
    },
    {
        $project: {
            _id: 0,
            group: "$_id",
            avg: { $divide: [ "$total", "$count" ] }
        }
    }
]).pretty()
user_77682870
Related