0

I have a query similar to the following;

    db.getCollection('list').aggregate([
    {
        $facet: {
            "events":[{
                $match: {
                   'type': 'Event'
                }
            }],
            "tasks": [{
                $match: {
                   'type': 'Task'
                }
            }]
        }
    },
    {$project: {activity:{$setUnion:['$events','$tasks']}}},
    {$unwind: '$activity'},
    {$replaceRoot: { newRoot: "$activity" }}
]);

One another easier approach to write this query is by using $or operator.

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

Source: https://gist.github.com/cthurston/7aead8229e10caa0be175babf7e8ddf1

The query that I'm working with is very similar, except I have other fields, not just 'type' going through this example. My idea is that, if I use $facet I'd get better performance if I create correct indexes for each query (that is inside $facet). On the other hand, if I use $or query as it is in the example, Mongo may struggle while trying to find the correct index for the query. Is this how it works?

Edit: It seems MongoDB can handle indexes for $or statements;

https://docs.mongodb.com/manual/reference/operator/query/or/#or-clauses-and-indexes

0

$facet - Index Use says:

The $facet stage, and its sub-pipelines, cannot make use of indexes, even if its sub-pipelines use $match or if $facet is the first stage in the pipeline. The $facet stage will always perform a COLLSCAN during execution.

$or Clauses and Indexes says:

When evaluating the clauses in the $or expression, MongoDB either performs a collection scan or, if all the clauses are supported by indexes, MongoDB performs index scans.


And, your query's $match stage:

$match: {
    $or: [
        { type: 'Event' }, 
        { type: 'Task' }
    ]
  }

can be simply written as:

$match: { type: { $in: [ 'Event', 'Task' ] } }

See $or versus $in says:

When using $or with that are equality checks for the value of the same field, use the $in operator instead of the $or operator.

Using the $or with proper indexing is likely to perform better than that of using the $facet stage.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.