MongoDB query with an or condition

Question

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

User · Answer

MongoDB query with an  or  condition db getCollection  movie   find   or    quot type quot   quot smartreply quot     quot category quot   quot small talk quot       MongoDB query with an  or    and    condition combined  db getCollection  movie   find   quot applicationId quot   quot 2b5958d9629026491c30b42f2d5256fa8 quot   or    quot type quot   quot smartreply quot     quot category quot   quot small talk quot

User · Answer

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

User · Answer

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  lt  Now    amp  amp  this expireTime  gt  Now      this expireTime    null

User · Answer

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

User · Answer

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

User · Answer

db Lead find       name      regex            Ravi                or           added by   arunkrishna aarux com            added by   aruna aarux com

[mongodb] MongoDB query with an 'or' condition

Examples related to mongodb