MongoDB aggregation match criteria to find if a field exists with value in array

Question

{
   "attributes" : [
                    {
                      "dept" : "accounts",
                      "location" : "onshore"
                    },
                    {
                      "dept" : "HR",
                      "location" : "offshore"
                    },
                    {
                      "dept" : "technology"
                      "location": "NL"
                    } 
                 ]
},
{
   "attributes" : [
                    {
                      "dept" : "accounts",
                      "location" : "onshore"
                    },
                    {
                      "dept" : "technology"
                      "location": "London"
                    } 
                 ]
},
{
   "attributes" : [
                    {
                      "dept" : "accounts",
                      "location" : "onshore"
                    },
                    {
                      "dept" : "HR"
                      "location": "London"
                    } 
                 ]
}

I want to get those documents where attributes array has dept :technology with location NOTequal to london or the attributes array does not have field dept :technology. So the final output will be like below:

{
   "attributes" : [
                    {
                      "dept" : "accounts",
                      "location" : "onshore"
                    },
                    {
                      "dept" : "HR",
                      "location" : "offshore"
                    },
                    {
                      "dept" : "technology"
                      "location": "NL"
                    } 
                 ]
},
{
   "attributes" : [
                    {
                      "dept" : "accounts",
                      "location" : "onshore"
                    },
                    {
                      "dept" : "HR"
                      "location": "London"
                    } 
                 ]
}

I have tried this solution but it gives me all the documents:

{
                                "attributes": {
                                    "$elemMatch": {
                                        "$or": [
                                            {
                                                "dept": {
                                                    "$nin": ["technology"]
                                                }
                                            },
                                            {
                                                "$and": [
                                                    {
                                                        "dept": "technology"
                                                    },
                                                    {
                                                        "location": {"$ne" : "London"}
                                                    }
                                                ]
                                            }
                                        ]
                                    }
                                }
                            }

You need to wrap $elemMatch with $and / $or not the other way around. This recent question might help you stackoverflow.com/questions/61614866/… — thammada.ts
– thammada.ts, Commented May 5, 2020 at 20:13

Valijon · Accepted Answer · 2020-05-06 03:11:01Z

1

Since $elemMatch evaluates each item of the array, we cannot use $nin or $ne operators if the condition is only to negate the boolean expression.

Instead, we should use the $not to performs a logical NOT operation on the specified < operator-expression >.

Try this one:

db.collection.find({
  "$or": [
    {
      "attributes": {
        $not: {
          "$elemMatch": {
            dept: "technology"
          }
        }
      }
    },
    {
      "attributes": {
        "$elemMatch": {
          dept: "technology",
          location: {
            $ne: "London"
          }
        }
      }
    }
  ]
})

MongoPlayground

edited May 6, 2020 at 3:11

answered May 6, 2020 at 2:46

Valijon

13.2k4 gold badges40 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MongoDB aggregation match criteria to find if a field exists with value in array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related