带有“ or”条件的 MongoDB 查询

所以我有一个嵌入式文档，可以追踪小组成员。每个嵌入式文档都有一个指向另一个集合中的组的 ID、一个开始日期和一个 可以选择过期日期。

我想查询组的当前成员。“ Current”表示开始时间小于当前时间，过期时间大于当前时间 OR null。

这个条件查询完全阻塞了我。我可以通过运行两个查询并合并结果来做到这一点，但这似乎很难看，需要一次加载所有结果。或者，我可以将过期时间默认设置为遥远未来的某个任意日期，但这似乎更加难看，而且可能很脆弱。在 SQL 中，我只用“(expires > = Now ()) OR (expires IS NULL)”来表示它——但是我不知道如何在 Mongo 中这样做。

有什么想法吗? 非常感谢提前。

146340 次浏览
小开

Query objects in Mongo by default AND expressions together. Mongo currently does not include an OR operator for such queries, however there are ways to express such queries.

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 ); } } );
小开

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.

小开
最佳答案

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}]}
小开

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"
}]
});
小开

db.Lead.find( 

{"name": {'$regex' : '.*' + "Ravi" + '.*'}},
{
"$or": [{
'added_by':"arunkrishna@aarux.com"
}, {
'added_by':"aruna@aarux.com"
}]
}

);

小开

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"}]})

提交答案