MongoDB: multiple $lookup between two collections

Question

I've two collections. Player:

    [{
  "_id": {
    "$oid": "6009cf73445ff54178c9f123"
  },
  "Nb_Player": 10,
  "Name_Player": "Roddick",
  "Nationality": "United States",
  "Score": 7,
  "Name_Tournament": "Roland Garros",
  "Gender": "Male"
},{
  "_id": {
    "$oid": "6009cf73445ff54178c9f124"
  },
  "Nb_Player": 10,
  "Name_Player": "Roddick",
  "Nationality": "United States",
  "Score": 8,
  "Name_Tournament": "Australia Open",
  "Gender": "Male"
},{
  "_id": {
    "$oid": "6009cf73445ff54178c9f125"
  },
  "Nb_Player": 10,
  "Name_Player": "Roddick",
  "Nationality": "United States",
  "Score": 8,
  "Name_Tournament": "Flusshing Meadows",
  "Gender": "Male"
},{
  "_id": {
    "$oid": "6009cf73445ff54178c9f126"
  },
  "Nb_Player": 10,
  "Name_Player": "Roddick",
  "Nationality": "United States",
  "Score": 0,
  "Name_Tournament": "Paris-Bercy Open",
  "Gender": "Male"
},{
  "_id": {
    "$oid": "6009cf73445ff54178c9f127"
  },
  "Nb_Player": 20,
  "Name_Player": "Ginepri",
  "Nationality": "United States",
  "Score": 8,
  "Name_Tournament": "Roland Garros",
  "Gender": "Male"
},{
  "_id": {
    "$oid": "6009cf73445ff54178c9f128"
  },
  "Nb_Player": 30,
  "Name_Player": "Gasquet",
  "Nationality": "France",
  "Score": 1,
  "Name_Tournament": "Australia Open",
  "Gender": "Male"
},{
  "_id": {
    "$oid": "6009cf73445ff54178c9f129"
  },
  "Nb_Player": 40,
  "Name_Player": "Montfils",
  "Nationality": "France",
  "Score": 3,
  "Name_Tournament": "Australia Open",
  "Gender": "Male"
},{
  "_id": {
    "$oid": "6009cf73445ff54178c9f12a"
  },
  "Nb_Player": 100,
  "Name_Player": "Mauresmo",
  "Nationality": "France",
  "Score": null,
  "Name_Tournament": null,
  "Gender": "Female"
},{
  "_id": {
    "$oid": "6009cf73445ff54178c9f12b"
  },
  "Nb_Player": 200,
  "Name_Player": "Davenport",
  "Nationality": "United States",
  "Score": 4,
  "Name_Tournament": "Roland Garros",
  "Gender": "Female"
}]

and Teams:

[{
  "_id": {
    "$oid": "6020251048a99f26045bb879"
  },
  "Nb_Team": 1,
  "Name_Player_1": "Roddick",
  "Name_Player_2": "Montfils",
  "Score": 9,
  "Name_Tournament": "Roland Garros"
},{
  "_id": {
    "$oid": "6020251048a99f26045bb87a"
  },
  "Nb_Team": 2,
  "Name_Player_1": "Ginepri",
  "Name_Player_2": "Gasquet",
  "Score": 7,
  "Name_Tournament": "Roland Garros"
},{
  "_id": {
    "$oid": "6020251048a99f26045bb87b"
  },
  "Nb_Team": 1,
  "Name_Player_1": "Roddick",
  "Name_Player_2": "Montfils",
  "Score": 7,
  "Name_Tournament": "Flusshing Meadows"
},{
  "_id": {
    "$oid": "6020251048a99f26045bb87c"
  },
  "Nb_Team": 2,
  "Name_Player_1": "Ginepri",
  "Name_Player_2": "Gasquet",
  "Score": 8,
  "Name_Tournament": "Flusshing Meadows"
}]

As a output I would like the players (Name_Player) of French Nationality playing in Teams as well as their Nb_Team. Something like this:

{ "_id" : { "Player" : "Montfils", "Nationality" : "France", "Nb_Team": 1 } }
{ "_id" : { "Player" : "Gasquet", "Nationality" : "France", "Nb_Team": 2 } }

I tried with 3 $lookup:

db.Player.aggregate([
    {
        "$lookup": {
            "from": "Teams",
            "localField": "Name_Player",
            "foreignField": "Name_Player_1",
            "as": "FrenchPlayerTeam1"
        }
    },
    {
        "$lookup": {
            "from": "Teams",
            "localField": "Name_Player",
            "foreignField": "Name_Player_2",
            "as": "FrenchPlayerTeam2"
        }
    },
    {
        "$lookup": {
            "from": "Teams",
            "localField": "Name_Tournament",
            "foreignField": "Name_Tournament",
            "as": "Name_Tournament"
        }
    },
      { "$match": {"Nationality": "France", "Score": {"$exists": true,"$ne": null}, "Name_Tournament": {"$exists": true,"$ne": null} }},
  { "$group": {
    "_id": {
    "Player":"$Name_Player",
    "Nationality": "$Nationality",
    "Nb_Team": "$Nb_Team"},
  }}    
  ]);

and the result is :

[ { _id: { Player: 'Gasquet', Nationality: 'France' } },
  { _id: { Player: 'Montfils', Nationality: 'France' } } ]

If I try to use a $unwind after the second $lookup I do not get any result at all. Is there a way to get the result I wish? Thanks

ray · Accepted Answer · 2021-11-21 09:12:02Z

1

Not sure if I am understanding your requirements correctly, but it seems that from your sample data both players will have Nb_Team:2.

Nevertheless, here is a Mongo Playground of query.

db.Player.aggregate([
  {
    "$match": {
      "Nationality": "France",
      "Score": {
        "$exists": true,
        "$ne": null
      }
    }
  },
  {
    "$lookup": {
      "from": "Teams",
      "let": {
        name: "$Name_Player"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $or: [
                {
                  $eq: [
                    "$Name_Player_1",
                    "$$name"
                  ]
                },
                {
                  $eq: [
                    "$Name_Player_2",
                    "$$name"
                  ]
                }
              ]
            }
          }
        },
        {
          "$count": "cnt"
        }
      ],
      "as": "Nb_Team"
    }
  },
  {
    "$unwind": "$Nb_Team"
  },
  {
    $project: {
      "_id": {
        "Player": "$Name_Player",
        "Nationality": "$Nationality",
        "Nb_Team": "$Nb_Team.cnt"
      }
    }
  }
])

The idea is to use $lookup sub-pipeline to manipulate the $lookup result. You can easily modify the query in $lookup sub-pipeline and $project to make it fit your needs.

edited Nov 21, 2021 at 9:12

answered Feb 15, 2021 at 2:56

ray

15.6k12 gold badges96 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Cai Over a year ago

Hi Ray, thanks for your help! I'll try it. The problem is that in the second collection (TEAMS), the two players of the collection PLAYER belong to Nb_Team = 1 and NB_Team = 2 respectively.

ray Over a year ago

Hi @Cai, the link to playground is updated. Now the pipeline use $first to extract the NB_team number. It is assumed that the Nb_team in Teams collection is same for a certain player.

Cai Over a year ago

Hi @ray I saw it thanks a lot. Your explanation helped me to fully understand how to use the pipeline with the $loohup in MongoDB. I truly appreciated it.

Collectives™ on Stack Overflow

MongoDB: multiple $lookup between two collections

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related