MongoDB query with an 'or' condition
MongodbMongodb Problem Overview
So I have an embedded document that tracks group memberships. Each embedded document has an ID pointing to the group in another collection, a start date, and an optional expire date.
I want to query for current members of a group. "Current" means the start time is less than the current time, and the expire time is greater than the current time OR null.
This conditional query is totally blocking me up. I could do it by running two queries and merging the results, but that seems ugly and requires loading in all results at once. Or I could default the expire time to some arbitrary date in the far future, but that seems even uglier and potentially brittle. In SQL I'd just express it with "(expires >= Now()) OR (expires IS NULL)" -- but I don't know how to do that in Mongo.
Any ideas? Thanks very much in advance.
Mongodb Solutions
Solution 1 - Mongodb
Just thought I'd update in-case anyone stumbles across this page in the future. As of 1.5.3, mongo now supports a real $or operator: http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-%24or
Your query of "(expires >= Now()) OR (expires IS NULL)" can now be rendered as:
{$or: [{expires: {$gte: new Date()}}, {expires: null}]}
Solution 2 - Mongodb
In case anyone finds it useful, www.querymongo.com does translation between SQL and MongoDB, including OR clauses. It can be really helpful for figuring out syntax when you know the SQL equivalent.
In the case of OR statements, it looks like this
SQL:
SELECT * FROM collection WHERE columnA = 3 OR columnB = 'string';
MongoDB:
db.collection.find({
"$or": [{
"columnA": 3
}, {
"columnB": "string"
}]
});
Solution 3 - Mongodb
MongoDB query with an 'or' condition
db.getCollection('movie').find({$or:[{"type":"smartreply"},{"category":"small_talk"}]})
MongoDB query with an 'or', 'and', condition combined.
db.getCollection('movie').find({"applicationId":"2b5958d9629026491c30b42f2d5256fa8",$or:[{"type":"smartreply"},{"category":"small_talk"}]})
Solution 4 - Mongodb
Use "in" or "where".
Its gonna be something like this:
db.mycollection.find( { $where : function() {
return ( this.startTime < Now() && this.expireTime > Now() || this.expireTime == null ); } } );
Solution 5 - Mongodb
db.Lead.find(
{"name": {'$regex' : '.*' + "Ravi" + '.*'}},
{
"$or": [{
'added_by':"[email protected]"
}, {
'added_by':"[email protected]"
}]
}
);
Solution 6 - Mongodb
Using a $where query will be slow, in part because it can't use indexes. For this sort of problem, I think it would be better to store a high value for the "expires" field that will naturally always be greater than Now(). You can either store a very high date millions of years in the future, or use a separate type to indicate never. The cross-type sort order is defined at here.
An empty Regex or MaxKey (if you language supports it) are both good choices.