我想将 building 与 floors 和 rooms 聚合:

给定以下 3 个集合:

/* 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")
}

我想一起查找它们并尝试使用以下查询:

db.getCollection('buildings').aggregate([
    {
        "$lookup": {
            "from": "floors",
            "localField": "_id",
            "foreignField": "buildingId",
            "as": "floors"
        }
    },
    {
        "$lookup": {
            "from": "rooms",
            "localField": "floors._id",
            "foreignField": "floorId",
            "as": "floors.rooms"
        }
    }
  ]);

结果如下:

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

但我会得到以下结果:

{
    "_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" : [ ]
        }
    ]
}

如您所见，我想查找所有引用资料以获取建筑结构及其楼层和房间。

我怎样才能做到这一点？

最佳答案

看一眼您的聚合 查询及其结果的输出类型，我觉得 不支持双重 $lookup(在另一个查找中查找) MongoDB(直到 3.4 版本)。因此，您最好的选择是使用 $unwind 并使结果更接近您的预期。

这里是查询:

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"
         }
     }
   ]);

及其输出:

{
        "_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" : [ ]
        }
}

关于MongoDB 聚合查找，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/46142330/