CODEWITHSUNDEEP
mongodbmongodb-query
tenbits
tenbits

Reputation: 8008

MongoDB $or operator for multiple fields separately

Looks like MongoDB supports $or operator only in the query root, and not per field query. So this doesn't work:

db.collection.find({
    foo: {
        $or: [ fooQuery1, fooQuery2 ]
    },
    bar: {
        $or: [ barQuery1, barQuery2 ]
    }
})

so we have to write it like:

db.collection.find({
    $or: [
        { foo: fooQuery1, bar: barQuery1 },
        { foo: fooQuery1, bar: barQuery2 },
        { foo: fooQuery2, bar: barQuery1 },
        { foo: fooQuery1, bar: barQuery2 },
    ]
})

Basically write all possible combinations for foo and bar. But this is insane when we have to query by >2 fields with >2 OR-statements each.

It is quite easy to write a function which takes the fields and its OR statements from the first example and to generate all possible variations from the second example. But is there some MongoDB native approach, may be we are missing here something.

And may be you know the reason, why the first approach is not supported? So that we better understand mongodb internals.

Thank you.

Upvotes: 2

Views: 1679

Answers (1)

Joe
Joe

Reputation: 28316

There are a couple of ways this query could be written, but it depends on the actual operations contained in each query.

If the queries are checking equality or a regular expression, you could use the $in operator, like

{
   foo: { $in: [ "fooValue1", /^fooPrefix/ ]},
   bar: { $in: [ "barValue1", "barValue2" ]}
}

If the subqueries have other tests, like inequality or existence, you could combine each field's queries in a separate $or, with an $and operator to ensure a match from each one, like:

{$and: [
   {$or: [{foo: fooQuery1}, {foo: fooQuery2}]},
   {$or: [{bar: barQuery1}, {bar: barQuery2}]}
]}

Upvotes: 2

Related Questions