In MongoDB C# how to get nested array to perform aggregation query on it

Question

I have a mongo database with two collections: Tournament and Player. A tournament contains, apart from simple attributes like _id and Name, a list of Match objects.

I have a query, in which I want to find the player with the most matches won. Before, I also had a collection Match in which all matches were contained. However I wanted to use the nested documents feature of MongoDB so I put those matches in Tournament instead.

The query before (one that worked):

var players = _matchCollection
    .Unwind<Match, Match>(m => m.WinnerId)
    .Group(m => m.WinnerId, g => new
    {
        Id = g.First().WinnerId,
        MatchesWon = g.Count()
    }).ToList();

Now, my attempts so far (gives no error, but the players sequence contains no objects):

var players = _tournamentCollection.Aggregate().Unwind<Tournament,Match>(tour => tour.Matches)
    .Unwind<Match, Match>(m => m.WinnerId)
    .Group(m => m.WinnerId, g => new
    {
        Id = g.First().WinnerId,
        MatchesWon = g.Count()
    }).ToList();

So my question is: how can I make such a query work on the nested array of matches?

EDIT: Added the relevant classes.

Tournament:

public class Tournament
{
    public string? Id { get; set; }
    public string? Name { get; set; }
    public string? Surface { get; set; }
    public int? DrawSize { get; set; }
    public string? Level { get; set; }
    public string? Date { get; set; }
    public List<Match> Matches { get; set; } = new();
}

Match:

public class Match
{
    public string? Id { get; set; }
    public string? MatchNum { get; set; }
    public string? WinnerId { get; set; }
    public string? LoserId { get; set; }
    public string? Score { get; set; }
    public string? BestOf { get; set; }
    public string? Round { get; set; }
    public string? TourneyId { get; set; }
}

Answer 1

The problem is due to these 2 $unwind stages. This would return an empty value as matches is a 1-level array, and winnerId is not an array but it is a string field.

.Unwind<Tournament,Match>(tour => tour.Matches)
.Unwind<Match, Match>(m => m.WinnerId)

---

Instead, you need to these steps:

1. Create a UnwindTournament class that flattens the Matches list.
2. Unwind from the Tournament document to the UnwindTournament document.
3. Group by Matches.WinnerId field.
4. Take grouped Key as Id.

public class UnwindTournament
{
    public ObjectId? Id { get; set; }
    public string? Name { get; set; }
    public string? Surface { get; set; }
    public int? DrawSize { get; set; }
    public string? Level { get; set; }
    public string? Date { get; set; }
    public Match Matches { get; set; } = new Match();
}

var players = _tournamentCollection.Aggregate()
    .Unwind<Tournament, UnwindTournament>(tour => tour.Matches)
    .Group(m => m.Matches.WinnerId, g => new
    {
        Id = g.Key,
        MatchesWon = g.Count()
    })
    .ToList();

Test Data

{
  "_id": {
    "$oid": "63685837e5453d791ed775f4"
  },
  "name": "Tournament FIFA",
  "matches": [
    {
      "winnerId": "1",
      "loserId": "2"
    },
    {
      "winnerId": "3",
      "loserId": "4"
    },
    {
      "winnerId": "1",
      "loserId": "3"
    }
  ]
}

Demo & Result