Sorting and ranking matching elements in a nested array list in MongoDB

Question

Greetings fellow fans of MongoDB!

I've got here a data structure with board game data where achieved scores (after every round) are tracked as nested arrays associated with the player's name. Note that with each board game there's a different set of players:

{
"BoardGames" : {
    "Game1" : {
        "players" : {
            "Anne" : [97, 165, 101, 67],
            "Pete" : [86, 115, 134, 149],
            "Scott" : [66, 89, 103, 74],
            "Jane" : [113, 144, 125, 99],
            "Katie" : [127, 108, 98, 151]
        }
    },
    "Game2" : {
        "players" : {
            "Margot" : [1, 0, 0, 0],
            "Pete" : [0, 0, 1, 1],
            "Michael" : [0, 0, 0, 0],
            "Jane" : [0, 0, 1, 0]
        }
    },
    "Game3" : {
        "players" : {
            "Chris" : [6, 2, 4, 0, 5, 7],
            "Pete" : [4, 5, 2, 5, 3, 1, 4],
            "Julia" : [3, 7, 4, 0],
            "Tom" : [3, 2, 4, 8, 2, 6, 7]
        }
    },
}

Game1: Players earn as many victory points per round as they can
Game2: Winning around earns 1, losing a round 0
Game3: Players may leave after every round, hence some players have played more rounds than others, so these arrays are different in their length

So, here are my questions:

Which player got the most points in each game? Who the least?
Who is the winner in the first round? 2nd round, etc.
Who is sitting on 1st, 2nd and 3rd rank from all played games?

I've done quite some queries with mongo, but so far with a nested array that is attached to a flexible/unpredictable parent node I have no idea how to write a query. Also, maybe this is not the best way how I structured the data. So in case you have a better idea, I'd be happy to learn!

Cheers!

P.S.: The insertMany statement to above JSON data:

db.boardGames.insertMany([
{
    "_id" : 1,
    "Game1" : {
        "players" : {
            "Anne" : [97, 165, 101, 67],
            "Pete" : [86, 115, 134, 149],
            "Scott" : [66, 89, 103, 74],
            "Jane" : [113, 144, 125, 99],
            "Katie" : [127, 108, 98, 151]
        }
    },
    "Game2" : {
        "players" : {
            "Margot" : [1, 0, 0, 0],
            "Pete" : [0, 0, 1, 1],
            "Michael" : [0, 0, 0, 0],
            "Jane" : [0, 0, 1, 0]
        }
    },
    "Game3" : {
        "players" : {
            "Chris" : [6, 2, 4, 0, 5, 7],
            "Pete" : [4, 5, 2, 5, 3, 1, 4],
            "Julia" : [3, 7, 4, 0],
            "Tom" : [3, 2, 4, 8, 2, 6, 7]
        }
    }
}]);

The schema structure is not very flexible as it is. With more data, querying will be difficult. How do you know/query that Chris has played in Game3 - for example. — prasad_
– prasad_, Commented Apr 8, 2021 at 3:21

Dĵ ΝιΓΞΗΛψΚ · Accepted Answer · 2021-04-08 12:40:45Z

the schema you have is not ideal. if it was something like this: https://mongoplayground.net/p/o8m205t9UKG then you can query like the following:

find winner of each game:

db.collection.aggregate(
[
    {
        $set: {
            Players: {
                $map: {
                    input: "$Players",
                    as: "x",
                    in: {
                        Name: "$$x.Name",
                        TotalScore: { $sum: "$$x.Scores" }
                    }
                }
            }
        }
    },
    {
        $unwind: "$Players"
    },
    {
        $sort: { "Players.TotalScore": -1 }
    },
    {
        $group: {
            _id: "$Name",
            Winner: { $first: "$Players.Name" }
        }
    }
])

find top 3 ranking players across all games:

db.collection.aggregate(
[
    {
        $set: {
            Players: {
                $map: {
                    input: "$Players",
                    as: "x",
                    in: {
                        Name: "$$x.Name",
                        TotalScore: { $sum: "$$x.Scores" }
                    }
                }
            }
        }
    },
    {
        $unwind: "$Players"
    },
    {
        $group: {
            _id: "$Players.Name",
            TotalScore: { $sum: "$Players.TotalScore" }
        }
    },
    {
        $sort: { TotalScore: -1 }
    },
    {
        $limit: 3
    },
    {
        $group: {
            _id: null,
            TopRanks: { $push: "$_id" }
        }
    },
    {
        $project: {
            _id: 0,
            TopRanks: 1
        }
    }
])

find the winner of each round across all games

db.collection.aggregate(
[
    {
        $set: {
            "Players": {
                $map: {
                    input: "$Players",
                    as: "p",
                    in: {
                        Scores: {
                            $map: {
                                input: "$$p.Scores",
                                as: "s",
                                in: {
                                    Player: "$$p.Name",
                                    Round: { $add: [{ $indexOfArray: ["$$p.Scores", "$$s"] }, 1] },
                                    Score: "$$s"
                                }
                            }
                        }
                    }
                }
            }
        }
    },
    {
        $unwind: "$Players"
    },
    {
        $unwind: "$Players.Scores"
    },
    {
        $replaceRoot: { newRoot: "$Players.Scores" }
    },
    {
        $sort: {
            Round: 1,
            Score: -1
        }
    },
    {
        $group: {
            _id: "$Round",
            Winner: { $first: "$Player" }
        }
    },
    {
        $project: {
            _id: 0,
            Round: "$_id",
            Winner: 1
        }
    },
    {
        $sort: {
            Round: 1
        }
    }
])

Thanks a lot! I already suspected that my structure was not ideal. I'll check your solution tonight and report back.

Collectives™ on Stack Overflow

Sorting and ranking matching elements in a nested array list in MongoDB

1 Answer 1

find winner of each game:

find top 3 ranking players across all games:

find the winner of each round across all games

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

find winner of each game:

find top 3 ranking players across all games:

find the winner of each round across all games

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related