Select All Of One Value and Another by Condition with Array

Question

I have these documents :

{
    "people" : [
        {
            "Gender" : "Male",
            "Age" : 31
        },
        {
            "Gender" : "Female",
            "Age" : 22
        }
    ]
}
{
    "people" : [
        {
            "Gender" : "Male",
            "Age" : 31
        },
        {
            "Gender" : "Female",
            "Age" : 24
        }
    ]
}
{ 
    "people" : [ 
        { 
            "Gender" : "Male",
            "Age" : 31
        }
    ]
}

Now I want to retrieve all the documents where IF there is a woman, then this woman must be 22. So it must return all Male gender regardless of the age and all Female gender where age = 22:

{
    "people" : [
        {
            "Gender" : "Male",
            "Age" : 31
        },
        {
            "Gender" : "Female",
            "Age" : 22
        }
    ]
}
{ 
    "people" : [ 
        { 
            "Gender" : "Male",
            "Age" : 31
        }
    ]
}

How can I do this?

Answer 1

You basically need an $or condition where one of the options negates "Female" from being a gender in any array element and the other condition only matches those which also have the age constraint using $elemMatch:

db.collection.find({
  "$or": [
    { 
      "people.Gender": { "$eq": "Male", "$ne": "Female" }
    },
    {
      "people": {
        "$elemMatch": {
          "Gender": "Female",
          "Age": 22
        }
      }
    }
  ]
})

Which returns your two documents:

{
        "_id" : ObjectId("5b06898bfb4739ed7db59c18"),
        "people" : [
                {
                        "Gender" : "Male",
                        "Age" : 31
                },
                {
                        "Gender" : "Female",
                        "Age" : 22
                }
        ]
}
{
        "_id" : ObjectId("5b06898bfb4739ed7db59c1a"),
        "people" : [
                {
                        "Gender" : "Male",
                        "Age" : 31
                }
        ]
}

Using "people.Gender" as a path for all values on the array we can test for "Male" with $eq and "Female" with $ne to mean there is "only" a male present.

The $or then allows the "alternate" constraint that IF the array element has a "Female" then their age must be 22. So you only get those with "just a male" and "joined with" results where the Female also met the age constraint in the element.