title: MongoDB: multiple $lookup between two collections
body: <p>I've two collections. Player:</p>
  <pre><code>    [{
    &quot;_id&quot;: {
      &quot;$oid&quot;: &quot;6009cf73445ff54178c9f123&quot;
    },
    &quot;Nb_Player&quot;: 10,
    &quot;Name_Player&quot;: &quot;Roddick&quot;,
    &quot;Nationality&quot;: &quot;United States&quot;,
    &quot;Score&quot;: 7,
    &quot;Name_Tournament&quot;: &quot;Roland Garros&quot;,
    &quot;Gender&quot;: &quot;Male&quot;
  },{
    &quot;_id&quot;: {
      &quot;$oid&quot;: &quot;6009cf73445ff54178c9f124&quot;
    },
    &quot;Nb_Player&quot;: 10,
    &quot;Name_Player&quot;: &quot;Roddick&quot;,
    &quot;Nationality&quot;: &quot;United States&quot;,
    &quot;Score&quot;: 8,
    &quot;Name_Tournament&quot;: &quot;Australia Open&quot;,
    &quot;Gender&quot;: &quot;Male&quot;
  },{
    &quot;_id&quot;: {
      &quot;$oid&quot;: &quot;6009cf73445ff54178c9f125&quot;
    },
    &quot;Nb_Player&quot;: 10,
    &quot;Name_Player&quot;: &quot;Roddick&quot;,
    &quot;Nationality&quot;: &quot;United States&quot;,
    &quot;Score&quot;: 8,
    &quot;Name_Tournament&quot;: &quot;Flusshing Meadows&quot;,
    &quot;Gender&quot;: &quot;Male&quot;
  },{
    &quot;_id&quot;: {
      &quot;$oid&quot;: &quot;6009cf73445ff54178c9f126&quot;
    },
    &quot;Nb_Player&quot;: 10,
    &quot;Name_Player&quot;: &quot;Roddick&quot;,
    &quot;Nationality&quot;: &quot;United States&quot;,
    &quot;Score&quot;: 0,
    &quot;Name_Tournament&quot;: &quot;Paris-Bercy Open&quot;,
    &quot;Gender&quot;: &quot;Male&quot;
  },{
    &quot;_id&quot;: {
      &quot;$oid&quot;: &quot;6009cf73445ff54178c9f127&quot;
    },
    &quot;Nb_Player&quot;: 20,
    &quot;Name_Player&quot;: &quot;Ginepri&quot;,
    &quot;Nationality&quot;: &quot;United States&quot;,
    &quot;Score&quot;: 8,
    &quot;Name_Tournament&quot;: &quot;Roland Garros&quot;,
    &quot;Gender&quot;: &quot;Male&quot;
  },{
    &quot;_id&quot;: {
      &quot;$oid&quot;: &quot;6009cf73445ff54178c9f128&quot;
    },
    &quot;Nb_Player&quot;: 30,
    &quot;Name_Player&quot;: &quot;Gasquet&quot;,
    &quot;Nationality&quot;: &quot;France&quot;,
    &quot;Score&quot;: 1,
    &quot;Name_Tournament&quot;: &quot;Australia Open&quot;,
    &quot;Gender&quot;: &quot;Male&quot;
  },{
    &quot;_id&quot;: {
      &quot;$oid&quot;: &quot;6009cf73445ff54178c9f129&quot;
    },
    &quot;Nb_Player&quot;: 40,
    &quot;Name_Player&quot;: &quot;Montfils&quot;,
    &quot;Nationality&quot;: &quot;France&quot;,
    &quot;Score&quot;: 3,
    &quot;Name_Tournament&quot;: &quot;Australia Open&quot;,
    &quot;Gender&quot;: &quot;Male&quot;
  },{
    &quot;_id&quot;: {
      &quot;$oid&quot;: &quot;6009cf73445ff54178c9f12a&quot;
    },
    &quot;Nb_Player&quot;: 100,
    &quot;Name_Player&quot;: &quot;Mauresmo&quot;,
    &quot;Nationality&quot;: &quot;France&quot;,
    &quot;Score&quot;: null,
    &quot;Name_Tournament&quot;: null,
    &quot;Gender&quot;: &quot;Female&quot;
  },{
    &quot;_id&quot;: {
      &quot;$oid&quot;: &quot;6009cf73445ff54178c9f12b&quot;
    },
    &quot;Nb_Player&quot;: 200,
    &quot;Name_Player&quot;: &quot;Davenport&quot;,
    &quot;Nationality&quot;: &quot;United States&quot;,
    &quot;Score&quot;: 4,
    &quot;Name_Tournament&quot;: &quot;Roland Garros&quot;,
    &quot;Gender&quot;: &quot;Female&quot;
  }]
  </code></pre>
  <p>and Teams:</p>
  <pre><code>[{
    &quot;_id&quot;: {
      &quot;$oid&quot;: &quot;6020251048a99f26045bb879&quot;
    },
    &quot;Nb_Team&quot;: 1,
    &quot;Name_Player_1&quot;: &quot;Roddick&quot;,
    &quot;Name_Player_2&quot;: &quot;Montfils&quot;,
    &quot;Score&quot;: 9,
    &quot;Name_Tournament&quot;: &quot;Roland Garros&quot;
  },{
    &quot;_id&quot;: {
      &quot;$oid&quot;: &quot;6020251048a99f26045bb87a&quot;
    },
    &quot;Nb_Team&quot;: 2,
    &quot;Name_Player_1&quot;: &quot;Ginepri&quot;,
    &quot;Name_Player_2&quot;: &quot;Gasquet&quot;,
    &quot;Score&quot;: 7,
    &quot;Name_Tournament&quot;: &quot;Roland Garros&quot;
  },{
    &quot;_id&quot;: {
      &quot;$oid&quot;: &quot;6020251048a99f26045bb87b&quot;
    },
    &quot;Nb_Team&quot;: 1,
    &quot;Name_Player_1&quot;: &quot;Roddick&quot;,
    &quot;Name_Player_2&quot;: &quot;Montfils&quot;,
    &quot;Score&quot;: 7,
    &quot;Name_Tournament&quot;: &quot;Flusshing Meadows&quot;
  },{
    &quot;_id&quot;: {
      &quot;$oid&quot;: &quot;6020251048a99f26045bb87c&quot;
    },
    &quot;Nb_Team&quot;: 2,
    &quot;Name_Player_1&quot;: &quot;Ginepri&quot;,
    &quot;Name_Player_2&quot;: &quot;Gasquet&quot;,
    &quot;Score&quot;: 8,
    &quot;Name_Tournament&quot;: &quot;Flusshing Meadows&quot;
  }]
  </code></pre>
  <p>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:</p>
  <pre><code>{ &quot;_id&quot; : { &quot;Player&quot; : &quot;Montfils&quot;, &quot;Nationality&quot; : &quot;France&quot;, &quot;Nb_Team&quot;: 1 } }
  { &quot;_id&quot; : { &quot;Player&quot; : &quot;Gasquet&quot;, &quot;Nationality&quot; : &quot;France&quot;, &quot;Nb_Team&quot;: 2 } }
  </code></pre>
  <p>I tried with 3 $lookup:</p>
  <pre><code>db.Player.aggregate([
      {
          &quot;$lookup&quot;: {
              &quot;from&quot;: &quot;Teams&quot;,
              &quot;localField&quot;: &quot;Name_Player&quot;,
              &quot;foreignField&quot;: &quot;Name_Player_1&quot;,
              &quot;as&quot;: &quot;FrenchPlayerTeam1&quot;
          }
      },
      {
          &quot;$lookup&quot;: {
              &quot;from&quot;: &quot;Teams&quot;,
              &quot;localField&quot;: &quot;Name_Player&quot;,
              &quot;foreignField&quot;: &quot;Name_Player_2&quot;,
              &quot;as&quot;: &quot;FrenchPlayerTeam2&quot;
          }
      },
      {
          &quot;$lookup&quot;: {
              &quot;from&quot;: &quot;Teams&quot;,
              &quot;localField&quot;: &quot;Name_Tournament&quot;,
              &quot;foreignField&quot;: &quot;Name_Tournament&quot;,
              &quot;as&quot;: &quot;Name_Tournament&quot;
          }
      },
        { &quot;$match&quot;: {&quot;Nationality&quot;: &quot;France&quot;, &quot;Score&quot;: {&quot;$exists&quot;: true,&quot;$ne&quot;: null}, &quot;Name_Tournament&quot;: {&quot;$exists&quot;: true,&quot;$ne&quot;: null} }},
    { &quot;$group&quot;: {
      &quot;_id&quot;: {
      &quot;Player&quot;:&quot;$Name_Player&quot;,
      &quot;Nationality&quot;: &quot;$Nationality&quot;,
      &quot;Nb_Team&quot;: &quot;$Nb_Team&quot;},
    }}    
    ]);
  </code></pre>
  <p>and the result is :</p>
  <pre><code>[ { _id: { Player: 'Gasquet', Nationality: 'France' } },
    { _id: { Player: 'Montfils', Nationality: 'France' } } ]
  </code></pre>
  <p>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</p>