CODEWITHSUNDEEP
mongodbmongodb-querylookup
Cai
Cai

Reputation: 33

MongoDB: multiple $lookup between two collections

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

Upvotes: 0

Views: 105

Answers (1)

ray
ray

Reputation: 15227

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.

Upvotes: 1

Related Questions