How to do I write a strongly typed filter where nested object properties cannot be equal using C# MongoDB driver?

Question

Model:{_id:1, Name:"Ross", Skills: [{ _id:1, SkillName:"Swiming"}, { _id:3, SkillName:"Fishing"}]}

Candidate1:{_id:1, Name:"Ross", Skills: [{ _id:1, SkillName:"Swiming"}, { _id:2, SkillName:"Kayaking"}]}
Candidate2:{_id:2, Name:"Ted", Skills: [{ _id:1, SkillName:"Swiming"}, { _id:3, SkillName:"Fishing"}]}
Candidate3:{_id:3, Name:"Snow", Skills: [{ _id:1, SkillName:"Swiming"}, { _id:1, SkillName:"Swiming"}]}
Candidate4:{_id:4, Name:"Snow", Skills: [ { _id:3, SkillName:"Fishing"}]}

I want to filter out the candidates who don't have the same skill more than once. after applying the filter my expected result is:

Candidate1:{_id:1, Name:"Ross", Skills: [{ _id:1, SkillName:"Swiming"}, { _id:2, SkillName:"Kayaking"}]}
Candidate2:{_id:2, Name:"Ted", Skills: [{ _id:1, SkillName:"Swiming"}, { _id:3, SkillName:"Fishing"}]}   
Candidate4:{_id:4, Name:"Snow", Skills: [ { _id:3, SkillName:"Fishing"}]}

Candidate3 will not be selected because he has the same skill twice.

So I tried this

filter = Builders<Candidate>.Filter.Where(cndt => cndt.Skills.Count>1 && cndt.Skills[0]._id != cndt.Skills[1]._id );

But this did not work. Note: There can be a maximum of two Skills

Answer 1

It's possible to do this by comparing the size of the skills array with a distinct array set of skills. This can be expressed in the mongo console with the following find query.

db.candidates.find({
  "$expr":{
    "$eq":[
      {
        "$size":{
          "$reduce":{
            "input":"$Skills",
            "initialValue":[
              
            ],
            "in":{
              "$setUnion":[
                "$$value",
                [
                  "$$this._id"
                ]
              ]
            }
          }
        }
      },
      {
        "$size":"$Skills"
      }
    ]
  }
}

This is fairly hard to express using the C# typed helper functions, so it's prob worth just passing in that as a string filter.

var client = new MongoClient();
var database = client.GetDatabase("test");
var collection = database.GetCollection<Candidate>("candidates");

var results = await collection.Find(@"{
  ""$expr"":{
    ""$eq"":[
      {
        ""$size"":{
          ""$reduce"":{
            ""input"":""$Skills"",
            ""initialValue"":[
              
            ],
            ""in"":{
              ""$setUnion"":[
                ""$$value"",
                [
                  ""$$this._id""
                ]
              ]
            }
          }
        }
      },
      {
        ""$size"":""$Skills""
      }
    ]
  }
}").ToListAsync();

foreach (var candidate in results)
{
   Console.WriteLine($"{candidate.Id}: {candidate.Name}");
}
// 1: Ross
// 2: Ted
// 4: Snow

It's fairly complex this query, so here's a breakdown of what it's doing

The $expr query operator allows us to use aggregation expressions in our query.

The $size gives us the size of an array, in which we can compare both sizes with a $eq.

Then we're creating a new array with a $reduce which will be a union set of all the values for the skill ids.