node.js - MongoDB Query with Multiple $or Operators Not Returning Expected Results - Stack Overflow

admin管理员组

文章数量:1431426

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!

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!

• node.js
• mongodb
• mongoose
• nosql

Share Improve this question Follow asked Nov 19, 2024 at 4:36 conradconrad 1061111 bronze badges 6

• are there any documents with visitDate in the future? – Joe Commented Nov 19, 2024 at 6:12
• 1 Quick playground showing that query works – Joe Commented Nov 19, 2024 at 6:21
• What type of data is stored in dateOfVisit and visitDate? – Markus Commented Nov 19, 2024 at 6:39
• @Markus dateOfVisit and visitDate both are of date type – conrad Commented Nov 19, 2024 at 8:31
• 1 In the query, you use strings for the comparison - you could try using ISODate, e.g. ISODate("2024-11-19T00:00:00Z") when providing the values. – Markus Commented Nov 19, 2024 at 9:40

 |  Show 1 more comment

2 Answers 2

Sorted by: Reset to default 1

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.

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!

本文标签： nodejsMongoDB Query with Multiple or Operators Not Returning Expected ResultsStack Overflow