Mongo Aggregation question

Working with Data
1

I need some help to overcome a problem i have.
I have 3 collections:
Leagues:
{_id: 'asdasd3ecas34', name: 'Premier League', country: 'England', level: 'A' }
Teams:
{_id: '1243jd1d12d22', name: 'Manchester United', league: 'asdasd3ecas34'}
Players:
{_id: ‘a129dH13dasdd’, name: ‘Christiano Ronaldo’, no: ‘7’ },
{_id: ‘a129dH13dasdA’, name: ‘Paul Pogba’, no: ‘6’ }

How can i list ALL the players from all teams that are in that competition (Premier League)

Thanks in advanced

2

Unless you have other fields in your collections that you are not showing us, you do not have

but an impossible task.

You have nothing in Teams that indicates who are the Players and you have nothing in Players that indicates their Teams. You either need:

  1. Players referring to Teams as
    { "_id" : "a129dH13dasdd" ,
      "name" : ... ,
      "no" : ... ,
      "team" : "1243jd1d12d22"
    }
  1. Teams referring to Players as
    { "_id" : "1243jd1d12d22" ,
      "name" : ... ,
      "league" : ... ,
      "players" : [ "a129dH13dasdd" , "a129dH13dasdA" , ... ]
    }

Your model seems to be an import from a SQL database and a column from Players has not been imported, or even a complete table of team to player relations.

The above 2 suggestions might not be the best MongoDB implementation but it is a starting point. For more ideas, see https://docs.mongodb.com/manual/core/data-modeling-introduction/

Untested aggregation for suggestion 1

match_league = { "$match" : { "name" : "Premier League" } }
lookup_teams = { "$lookup" : { "from" : "Teams" , "localField" : "_id" , "foreignField" : "league" , "as" : "teams"  } }
unwind_teams = { "$unwind" : { "path" : "teams" } }
lookup_players = { "$lookup" : { "from" : "Players" , "localField" : "teams._id" , "foreignField" : "team" , "as" : "players"  } }
unwind_players = { "$unwind" : { "path" : "players" } }
aggregation =
  [
    match_league ,
    lookup_teams ,
    unwind_teams ,
    lookup_players ,
    unwind_players
  ]

/* You get one document per player with team and league repeated.  You may
   use other stages like $replaceRoot to remove duplicated team and league.
*/
1 Like
3

I am really sorry about wrong display of data.
Actually the players are as follow:
{_id: ‘a129dH13dasdd’, name: ‘Christiano Ronaldo’, no: ‘7’ , teamId: ‘1243jd1d12d22’},
{_id: ‘a129dH13dasdA’, name: ‘Paul Pogba’, no: ‘6’ , teamId: ‘1243jd1d12d22’}

They are linked to the team

4

Then, take

and use teamId instead of team as foreignField.

And please read Formatting code and log snippets in posts

1 Like
6

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.