MongoDB aggregation lookup

Question

I would like to aggregate building with floors and rooms:

Given the 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 would like to lookup them together and tried it with the 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 the following result:

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

But I would have the 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 you see I would like to lookup all the references to get the building structure with it's floors and rooms.

How can I achieve that?

RLD · Accepted Answer · 2017-09-10 17:04:38Z

A glance at your aggregate query and the the kind of output it results makes me feel double $lookup (lookup inside another lookup) is not supported by MongoDB(till 3.4 version). So your best bet is to use $unwind and get the results closer to your expectation.

Here is the 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 its 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" : [ ]
        }
}

Hativ · Accepted Answer · 2017-09-10 19:40:48Z

1

$unwind makes floors $lookupable and $group adds them together again.

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"
        }
    },
    {
        "$group": {
            "_id": "$_id",
            "name": { "$first": "$name" },
            "floors": { "$push": "$floors" }
        }
    }]);

The result is exactly what I requested (see question).

answered Sep 10, 2017 at 19:40

Hativ

1,5301 gold badge16 silver badges24 bronze badges

1 Comment

Hativ Over a year ago

I will, but I have to wait 48 hours after asking the question until I can do that.

Collectives™ on Stack Overflow

MongoDB aggregation lookup

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related