This query is returning the first object but it should not return. Because it has the BU but in different domain. Its doing fine in single objects in collaborators. When there is multiple Its not behaving as expected. How can we do this any suggestions?

My criteria is In the collaborator array

• Only BU name or
• Only Domain or
• Both BU and Domain it should return.

In below situation first one has same domain <{"domain": "xyz.com"}> but still its not returning. Why?

[
  {
    name: "1",
    collaborators: [
      {
        "domain": "xyz.com"
      },
      {
        "buName": "Vignesh B"
      },
      {
        "domain": "yz.com"
      },
      {
        "domain": "xyz.com",
        "buName": "Vignesh B"
      }
    ]
  },
  {
    name: "2",
    collaborators: [
      {
        "domain": "xyz.com",
        "buName": "Vignesh BU"
      }
    ]
  },
  {
    name: "3",
    collaborators: [
      {
        "domain": "xyz.com"
      }
    ]
  },
  {
    name: "4",
    collaborators: [
      {
        "buName": "Vignesh BU"
      },
      {
        "domain": "xyz.com"
      },
      {
        "domain": "xyz.com",
        "buName": "Vignesh BU"
      }
    ]
  }
]

 

    db.collection.find({
  $or: [
    {
      "collaborators.domain": "xyz.com",
      "collaborators.buName": {
        "$exists": false
      }
    },
    {
      "collaborators.buName": "Vignesh BU",
      "collaborators.domain": {
        "$exists": false
      }
    },
    {
      "collaborators.buName": "Vignesh BU",
      "collaborators.domain": "xyz.com"
    }
  ]
})

CodePudding user response：

It is not returning the first document because the buName values in this document are "Vignesh B" and not "Vignesh BU". Only add an U in Vignesh B and it works.

Link to mongodb playground

CodePudding user response：

I think there was a comment at wone point that said that the name: "1" document was expected to return (as it matches the second "Only Domain" criteria) but it is not currently. This is because you will need to use the $elemMatch operator since you are querying an array with multiple conditions.

The query should look as follows, as demonstrated in this playground example (note that I've changed the name: 3 document so that it would not match):

db.collection.find({
  $or: [
    {
      "collaborators": {
        $elemMatch: {
          "domain": "xyz.com",
          "buName": {
            "$exists": false
          }
        }
      }
    },
    {
      "collaborators": {
        $elemMatch: {
          "buName": "Vignesh BU",
          "domain": {
            "$exists": false
          }
        }
      }
    },
    {
      "collaborators": {
        $elemMatch: {
          "buName": "Vignesh BU",
          "domain": "xyz.com"
        }
      }
    }
  ]
})

Why is this change needed? It is because of the semantics of how querying an array works in MongoDB. When querying on multiple nested conditions without using $elemMatch you are telling the database that different entries in the array can each individually satisfy the requirements. As shown in this playground example, that means that when you run this query:

db.collection.find({
  "arr.str": "abc",
  "arr.int": 123
})

The following document will match:

  {
    _id: 1,
    arr: [
      {
        str: "abc"
      },
      {
        int: 123
      }
    ]
  }

This is because the first entry in the array satisfies one of the query predicates while the other entry in the array satisfies the second predicate. Changing the query to use $elemMatch changes the semantics to specify that a single entry in the array must successfully satisfy all query predicate conditions which prevents the document above from matching.

In your specific situation the same thing was happening with your first set of conditions of:

    {
      "collaborators.domain": "xyz.com",
      "collaborators.buName": {
        "$exists": false
      }
    }

The first array item in the name: "1" document was matching the collaborators.domain condition. The problem was the second condition. While that same first array entry did not have a buName field, two of the other entries in the array did. Since there is no $elemMatch present, the database checked those other entries, found that the buName existed there, and that caused the query predicates to fail to match and for the document to not get returned. Adding the $elemMatch forces both of those checks to happen against the single entry in the array hence resolving the issue.