Skip to content

Instantly share code, notes, and snippets.

@cthurston
Created November 3, 2017 14:21
Show Gist options
  • Save cthurston/7aead8229e10caa0be175babf7e8ddf1 to your computer and use it in GitHub Desktop.
Save cthurston/7aead8229e10caa0be175babf7e8ddf1 to your computer and use it in GitHub Desktop.
MongoDb combine $facet results into a single result set.
db.getCollection('list').aggregate([
{
$facet: {
"events":[{
$match: {
'type': 'Event'
}
}],
"tasks": [{
$match: {
'type': 'Task'
}
}]
}
},
{$project: {activity:{$setUnion:['$events','$tasks']}}},
{$unwind: '$activity'},
{$replaceRoot: { newRoot: "$activity" }}
]);
@famence
Copy link

famence commented Dec 11, 2019

Thank you!

@HlibDerbenov
Copy link

I would like to give my 2 cents that this example of query is not the best one. Most often you'll use $setUnion to combine data of similar nature (in this case: from the same collection). But there are also many cases when different data combining will create you some problems as it will be now stored in one single array.

As for the query: there's no need to develop such a complicated query. This one will give you the same result:

db.getCollection('list').aggregate([{
    $match: {
        $or: [{
            type: 'Event'
        }, {
            type: 'Task'
        }]
    }
}]);

or even

db.getCollection('list').aggregate([{
    $match: {
        type: {$in: ['Event', 'Task']}
    }
}]);

And yes, I understand that's just an example of using $facet + $setUnion.

@cthurston
Copy link
Author

The scenario is not contrived. Salesforce has an activity type from which they extend to create events and tasks. However, often times you want to display all activity on a particular record, sorted correctly by some criteria. If you don't do it in the query using facet and setUnion you end up having to do extra logic on your server or in your client.

@olafkotur
Copy link

This was very useful, thank you 🥇 💯

@HatemSat
Copy link

Thanks for this one !

@hailazy
Copy link

hailazy commented Oct 8, 2021

This was very useful, thank you!

@jawadwaseem-10p
Copy link

How would you apply skip and limit on the combined results?

@shailOverjet
Copy link

How would you apply skip and limit on the combined results?

@vresetnikov
Copy link

The scenario is not contrived. Salesforce has an activity type from which they extend to create events and tasks. However, often times you want to display all activity on a particular record, sorted correctly by some criteria. If you don't do it in the query using facet and setUnion you end up having to do extra logic on your server or in your client.

The documentation states that $setUnion removes duplicates. How does that affect the final output if the output from both $facet stages contains fields with identical names?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment