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; }
}

I think providing a sample document / the complete classes for Tournament to the question will be helpful. — Yong Shun
– Yong Shun, Commented Nov 4, 2022 at 0:53
@YongShun Thanks for the feedback, I edited my post to add the relevant classes. — Davrozz
– Davrozz, Commented Nov 6, 2022 at 11:01

Yong Shun · Accepted Answer · 2022-11-07 01:47:24Z

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:

Create a UnwindTournament class that flattens the Matches list.
Unwind from the Tournament document to the UnwindTournament document.
Group by Matches.WinnerId field.
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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related