MongoDB aggregation lookup -

i aggregate building floors , rooms:

given following 3 collections:

/* buildings */ {     "_id" : objectid("59a09abe388f595b15bb5fa6"),     "name" : "home" }  /* floors */ {     "_id" : objectid("59a09abe388f595b15bb5fa3"),     "buildingid" : objectid("59a09abe388f595b15bb5fa6"),     "name" : "upstairs" } {     "_id" : objectid("59a09abe388f595b15bb5fa2"),     "buildingid" : objectid("59a09abe388f595b15bb5fa6"),     "name" : "downstairs" }  /* rooms */ {     "_id" : objectid("59a09bce388f595b15bb5fb6"),     "floorid" : objectid("59a09abe388f595b15bb5fa3"),     "name" : "bathroom",     "_userid" : objectid("590a08dba07c1a1bee87b310") } {     "_id" : objectid("59a09bce388f595b15bb5fc6"),     "floorid" : objectid("59a09abe388f595b15bb5fa3"),     "name" : "living room",     "_userid" : objectid("590a08dba07c1a1bee87b310") } 

i lookup them , tried following query:

db.getcollection('buildings').aggregate([     {         "$lookup": {             "from": "floors",             "localfield": "_id",             "foreignfield": "buildingid",             "as": "floors"         }     },     {         "$lookup": {             "from": "rooms",             "localfield": "floors._id",             "foreignfield": "floorid",             "as": "floors.rooms"         }     }   ]); 

this gives following result:

{     "_id" : objectid("59a09abe388f595b15bb5fa6"),     "_userid" : objectid("590a08dba07c1a1bee87b310"),     "name" : "home",     "floors" : {         "rooms" : []     } } 

but have following result:

{     "_id" : objectid("59a09abe388f595b15bb5fa6"),     "_userid" : objectid("590a08dba07c1a1bee87b310"),     "name" : "home",     "floors" : [          {             "_id" : objectid("59a09abe388f595b15bb5fa3"),             "buildingid" : objectid("59a09abe388f595b15bb5fa6"),             "name" : "upstairs",             "rooms": [             {                      "_id" : objectid("59a09bce388f595b15bb5fb6"),                     "floorid" : objectid("59a09abe388f595b15bb5fa3"),                     "name" : "bathroom"                 },                 {                     "_id" : objectid("59a09bce388f595b15bb5fc6"),                     "floorid" : objectid("59a09abe388f595b15bb5fa3"),                     "name" : "living room"                 }             ]         },          {             "_id" : objectid("59a09abe388f595b15bb5fa2"),             "buildingid" : objectid("59a09abe388f595b15bb5fa6"),             "name" : "downstairs",             "rooms" : [ ]         }     ] } 

as see lookup references building structure it's floors , rooms.

how can achieve that?

a glance @ aggregate query , the kind of output results makes me feel double $lookup (lookup inside lookup) not supported mongodb(till 3.4 version). best bet use $unwind , results closer expectation.

here query:

db.getcollection('buildings').aggregate([      {          "$lookup": {              "from": "floors",              "localfield": "_id",              "foreignfield": "buildingid",              "as": "floors"          }      },      {"$unwind":"$floors"},      {          "$lookup": {              "from": "rooms",              "localfield": "floors._id",              "foreignfield": "floorid",              "as": "floors.rooms"          }      }    ]); 

and output:

{         "_id" : objectid("59a09abe388f595b15bb5fa6"),         "name" : "home",         "floors" : {                 "_id" : objectid("59a09abe388f595b15bb5fa3"),                 "buildingid" : objectid("59a09abe388f595b15bb5fa6"),                 "name" : "upstairs",                 "rooms" : [                         {                                 "_id" : objectid("59a09bce388f595b15bb5fb6"),                                 "floorid" : objectid("59a09abe388f595b15bb5fa3"),                                 "name" : "bathroom",                                 "_userid" : objectid("590a08dba07c1a1bee87b310")                         },                         {                                 "_id" : objectid("59a09bce388f595b15bb5fc6"),                                 "floorid" : objectid("59a09abe388f595b15bb5fa3"),                                 "name" : "living room",                                 "_userid" : objectid("590a08dba07c1a1bee87b310")                         }                 ]         } } {         "_id" : objectid("59a09abe388f595b15bb5fa6"),         "name" : "home",         "floors" : {                 "_id" : objectid("59a09abe388f595b15bb5fa2"),                 "buildingid" : objectid("59a09abe388f595b15bb5fa6"),                 "name" : "downstairs",                 "rooms" : [ ]         } }