1. Introducción

En el siguiente post veremos como hacer joins entre documentos en MongoDB haciendo uso de la función lookup.

2. Colecciones

Colección de usuarios

db.user.insert({"_id":"1","name":"admin","surname":"admin","gender":"male","roles":["ROLE_ADMIN"]})
db.user.insert({"_id":"2","name":"Jorge","surname":"HernándezRamírez","gender":"male","roles":["ROLE_ADMIN"],"teams":[{"name":"UD.LasPalmas","sport":"Football"},{"name":"RealMadrid","sport":"Football"},{"name":"McLaren","sport":"F1"}]})
db.user.insert({"_id":"3","name":"Jose","gender":"male","surname":"HernándezRamírez","roles":["ROLE_USER"],"teams":[{"name":"UD.LasPalmas","sport":"Football"},{"name":"MagnusCarlsen","sport":"Chess"}]})
db.user.insert({"_id":"4","name":"Raul","surname":"GonzálezBlanco","gender":"male","roles":["ROLE_USER"],"teams":[{"name":"RealMadrid","sport":"Football"},{"name":"RealMadrid","sport":"Basketball"}]})
db.user.insert({"_id":"5","name":"Constanza","surname":"RamírezRodríguez","gender":"female","roles":["ROLE_USER"],"teams":[{"name":"UD.LasPalmas","sport":"Football"}]})

Colección de cuentas

db.account.insert({"_id":"1","userId":"1","account":"ES9121000418450200051332"})
db.account.insert({"_id":"2","userId":"1","account":"ES1800491500042710151321"})
db.account.insert({"_id":"3","userId":"2","account":"ES3320805801143040000499"})

3. Lookup

Simulamos un left join entre user y account

db.user.aggregate([
    {
      $lookup:
        {
          from: "account",
          localField: "_id",
          foreignField: "userId",
          as: "accounts"
        }
   },
   {
      $project:
        {_id: 0, surname: 0, gender: 0, roles: 0}
   }
]);

Obtenemos 

{ "name" : "admin", "accounts" : [ { "_id" : "1", "userId" : "1", "account" : "ES9121000418450200051332" }, { "_id" : "2", "userId" : "1", "account" : "ES1800491500042710151321" } ] }
{ "name" : "Jorge", "teams" : [ { "name" : "UD. Las Palmas", "sport" : "Football" }, { "name" : "Real Madrid", "sport" : "Football" }, { "name" : "McLaren", "sport" : "F1" } ], "accounts" : [ { "_id" : "3", "userId" : "2", "account" : "ES3320805801143040000499" } ] }
{ "name" : "Jose", "teams" : [ { "name" : "UD. Las Palmas", "sport" : "Football" }, { "name" : "Magnus Carlsen", "sport" : "Chess" } ], "accounts" : [ ] }
{ "name" : "Raul", "teams" : [ { "name" : "Real Madrid", "sport" : "Football" }, { "name" : "Real Madrid", "sport" : "Basketball" } ], "accounts" : [ ] }
{ "name" : "Constanza", "teams" : [ { "name" : "UD. Las Palmas", "sport" : "Football" } ], "accounts" : [ ] }

Simulamos un inner join entre user y account

db.user.aggregate([
    {
      $lookup:
        {
          from: "account",
          localField: "_id",
          foreignField: "userId",
          as: "accounts"
        }
   },
   {
      $match: {accounts: {$ne: []}}
   },
   {
      $project:
        {_id: 0, surname: 0, gender: 0, roles: 0, teams: 0}
   }
]);

Obtenemos 

{ "name" : "admin", "accounts" : [ { "_id" : "1", "userId" : "1", "account" : "ES9121000418450200051332" }, { "_id" : "2", "userId" : "1", "account" : "ES1800491500042710151321" } ] }
{ "name" : "Jorge", "accounts" : [ { "_id" : "3", "userId" : "2", "account" : "ES3320805801143040000499" } ] }