Mongodb Question posted in
The official documentation can be found here.

MongoDB Query with Multiple $or Operators Not Returning Expected Results

Original Query Works but Adding Additional $or Condition Fails

I have a MongoDB query that successfully returns 2 results:

{
  "state": "Review",
  "type": "Test",
  "subtype": { "$in": ["dr_visit", "annual_physical", "psychiatric_visit"] },
  "archived": false,
  "data.visitInformation.dateOfVisit": { "$gte": "2024-11-19T00:00:00.000Z" },
  "deleted": false,
  "$or": [
    { "account": "5dad84a4de014f0011999e27" },
    { "referral.user": "66cd7113e3887e0015522f05", "referral.accepted": true },
    { "referral.account": "5dad84a4de014f0011999e27", "referral.accepted": true }
  ]
}

This query works and retrieves the expected 2 documents.

Now, I want to modify the query to include another condition: check either data.visitInformation.dateOfVisit or data.overview.visitDate in the $or clause. These two fields are never present in the same document—they are part of different document types. However, when I try the following query, it returns no results:

{
  "state": "Review",
  "type": "Test",
  "subtype": { "$in": ["dr_visit", "annual_physical", "psychiatric_visit"] },
  "archived": false,
  "deleted": false,
  "$and": [
    {
      "$or": [
        { "account": "5dad84a4de014f0011999e27" },
        { "referral.user": "66cd7113e3887e0015522f05", "referral.accepted": true },
        { "referral.account": "5dad84a4de014f0011999e27", "referral.accepted": true }
      ]
    },
    {
      "$or": [
        { "data.visitInformation.dateOfVisit": { "$gte": "2024-11-19T00:00:00.000Z" } },
        { "data.overview.visitDate": { "$gte": "2024-11-19T00:00:00.000Z" } }
      ]
    }
  ]
}

Problem

This query returns no results even though the original query works. I suspect it’s related to how MongoDB handles $and and $or combinations, especially since data.visitInformation.dateOfVisit and data.overview.visitDate belong to different document types.

Question

Is there a way to achieve the desired result, where the query:

  • Matches documents with data.visitInformation.dateOfVisit or data.overview.visitDate,
  • And satisfies the $or conditions for the account or referral fields?

Any suggestions or insights are much appreciated!

Tags:

2

Answers


  1. 0

    MongoDB doesn’t require both sets of conditions to be true at the same time. Instead, we’ll combine everything into a single $or at the top leve

    {
  "state": "Review",
  "type": "Test",
  "subtype": { "$in": ["dr_visit", "annual_physical", "psychiatric_visit"] },
  "archived": false,
  "deleted": false,
  "$or": [
    {
      "$and": [
        {
          "$or": [
            { "account": "5dad84a4de014f0011999e27" },
            { "referral.user": "66cd7113e3887e0015522f05", "referral.accepted": true },
            { "referral.account": "5dad84a4de014f0011999e27", "referral.accepted": true }
          ]
        },
        {
          "$or": [
            { "data.visitInformation.dateOfVisit": { "$gte": "2024-11-19T00:00:00.000Z" } },
            { "data.overview.visitDate": { "$gte": "2024-11-19T00:00:00.000Z" } }
          ]
        }
      ]
    }
  ]
}

    What’s Happening Now:
    The top-level $or says "either this set of conditions matches, or that set of conditions matches."
    The first set of conditions checks the account or referral fields.
    The second set checks the date fields (dateOfVisit or visitDate).
    This way, if a document matches either the account/referral conditions or the date conditions, it will be returned.
    Why This Works:
    By grouping the two $or conditions into one $or at the top level, MongoDB is no longer trying to match both the account/referral and the date conditions at the same time. It just needs to match one of those sets of conditions.

    Bottom Line:
    This should allow the query to return documents that satisfy either the account/referral criteria or the date criteria. Try this query and see if it works as expected!

    Let me know if that clears things up or if you run into any issues!

    Login or Signup to reply.
  2. 0

    In the comments, you have specified that the properties data.visitInformation.dateOfVisit and data.overview.visitDate store data as date data type. In the query, a string is provided as comparison for $gte. This compares different data types, which does not lead to the expected results in the vast majority of the cases.

    Instead of providing strings for the $gte comparison, use the ISODate helper, e.g. ISODate('2024-11-19T00:00:00.000Z'). This way, only date data types are compared with one another.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search