How to find items in a collection that nested array field contains specific string using mongodb .net driver C#?

Question

I have ClassRoom documents in my collection and I have an array of Students in each ClassRoom. How can I find classes that have students who his/her first name or last name contains a specific string? Note: I want to implement it using filter builder, not Linq nor aggregation pipeline.

[
  {
    _id: 1,
    name: 'Room1',
    students: 
    [
      {
        _id: 1,
        firstName: 'John Junior',
        lastName: 'Smith'
       },
       {
        _id: 2,
        firstName: 'Jane',
        lastName: 'Jones'
       }
    ]
  },
  {
    _id: 2,
    name: 'Room2',
    students: 
    [
      {
        _id: 3,
        firstName: 'Mike',
        lastName: 'John'
       },
       {
        _id: 4,
        firstName: 'Kate',
        lastName: 'Doe'
       }
    ]
  },
{
    _id: 3,
    name: 'Room3',
    students: 
    [
      {
        _id: 5,
        firstName: 'Johnathan',
        lastName: 'Davis'
       }
    ]
  }
]

e.g. if we get the string john the query returns all classrooms.

Answer 1

You can combine an $elemMatch with a $regex, e.g.:

// Define the regex to look for
var regex = new Regex("john", RegexOptions.IgnoreCase);
// Set up the filter
var filter = Builders<Class>.Filter.ElemMatch(
    x => x.Students, 
    Builders<Student>.Filter.Or(
        Builders<Student>.Filter.Regex(x => x.FirstName, regex),
        Builders<Student>.Filter.Regex(x => x.LastName, regex)
    ));
// Read results
var result = await (await classes.FindAsync(filter)).ToListAsync();

Using a regex is not the best option in terms of performance; if it is too slow, you can also have a look at full-text-search. For details see this link.