newbie to CosmoDB how to query collection with multiple values?-CodePudding

I have the following collection and I want to query based on Class and FullName from Students

{
    "id" : "ABCD",
    "Class" : "Math",
    "Students" : [
        {
            "FullName" : "Dan Smith",
        },
        {
            "FullName" : "Dave Jackson",
        },


    ]
}

The following filter works based on class.

var filter = builder.Eq(x => x.Class, "Math");
var document = collection.Find(filter).FirstOrDefaultAsync();

But I want to query based on student also, I tried to add another filter and it has "Cannot implicitly convert type string to bool" error

filter &= builder.Eq(x => x.Students.Any(y => y.FullName,"Dan"));

CodePudding user response：

As you want to query with the nested document in an array, you need $elemMatch operator. In MongoDB .NET Driver syntax, you can achieve with either of these ways:

Solution 1: ElemMatch with LINQ Expression

filter &= builder.ElemMatch(x => x.Students, y => y.FullName == "Dan");

Solution 2: ElemMatch with FilterDefinition

filter &= builder.ElemMatch(x => x.Students, 
    Builders<Student>.Filter.Eq(y => y.FullName, "Dan"));

The above methods will return no document as the filter criteria don't match with the attached document.

If you look for matching the partial word, you need to work with $regex operator.

Solution: With regex match

filter &= builder.ElemMatch(x => x.Students, 
    Builders<Student>.Filter.Regex(y => y.FullName, "Dan"));

Demo