MongoDB : Find document where none of the subdocument matches criteria

Question

I want to find out document where Record A has "FLD1" and "FLD1" is not equal to "VAL2", it means it should also return document where either Record A does not exist or either FLD1 does not exist apart from FLD1 is not equal to VAL2.

In following document, It should return Document 1, 3, and 4

• 1: because Record A exists, FLD1 exists but Value of FLD1 is not VAL2
• 3: because Record A does not exists
• 4: because Record A exists but FLD1 does not exists

only 2 where Record A exists, FLD1 exists and value of FLD1 is equal to VAL2.

{
    _id:NumberLong("1"),
    Name:"Doc1",
    Records: [ 
        {
            "RecordName" : "Record A",
            "State" : 1,
            "Properties" : {
                "FLD1" : "VAL1"
            }
        },
        {
            "RecordName" : "Record B",
            "State" : 1,
            "Properties" : {
                "FLD2" : "VAL3",
                "FLD3" : "VAL2"
            }
        }
    ]
},
{
    _id:NumberLong("2"),
    Name:"Doc2",
    Records: [ 
        {
            "RecordName" : "Record A",
            "State" : 1,
            "Properties" : {
                "FLD1" : "VAL2"
                "FLD4" : "VAL1"
                "FLD5" : "VAL6"
            }
        },
        {
            "RecordName" : "Record C",
            "State" : 1,
            "Properties" : {
                "FLD1" : "VAL1",
                "FLD2" : "VAL3"
            }
        }
    ]
},
{
    _id:NumberLong("3"),
    Name:"Doc3",
    Records: [ 
        {
            "RecordName" : "Record B",
            "State" : 0,
            "Properties" : {
                "FLD2" : "VAL2"
                "FLD3" : "VAL4"
                "FLD4" : "VAL5"
            }
        },
        {
            "RecordName" : "Record C",
            "State" : 1,
            "Properties" : {
                "FLD3" : "VAL2",
                "FLD5" : "VAL4"
            }
        }
    ]
},
{
    _id:NumberLong("4"),
    Name:"Doc4",
    Records: [ 
        {
            "RecordName" : "Record A",
            "State" : 1,
            "Properties" : {
                "FLD2" : "VAL2"
            }
        },
        {
            "RecordName" : "Record C",
            "State" : 1,
            "Properties" : {
                "FLD3" : "VAL3",
                "FLD4" : "VAL4"
            }
        }
    ]
}

Does any one know how to write such query for mongo db?

Also, If I want to find out all the document where none of the sub document has Record Name with value Record B, How would I find out?

In that case, query should return document 2 and 4.

Answer 1

It looks like you need $or operator embracing three separate filtering conditions:

db.collection.find({
    $or: [
        { "Records": { $not : { $elemMatch: { "RecordName": "Record A" } } } },
        { "Records.Properties.FLD1": { $exists: false } },
        { "Records": { $not : { $elemMatch: { "Properties.FLD1": "VAL2" } } } }
    ]
})

$elemMatch along with $not allows you to check if all Records have RecordName not equal to Record A, then you can use $exists to check FLD1 and another $elemMatch with $not:

Mongo Playground