mongodb - 从mongodb中的三个集合中获取结果

Question

我有三个集合包含如下数据，

客户收藏

{ 
    "_id" : ObjectId("5a058e316803fafd127b23c9"), 
    "client_name" : "client A", 
    "client_status" : "A"
}
{ 
    "_id" : ObjectId("5a058e316803fafd127b23cb"), 
    "client_name" : "client B", 
    "client_status" : "A"
}

池集合

{ 
    "_id" : ObjectId("5a0e76f66803fa530a7b23d4"), 
    "pool_name" : "pool A", 
    "pool_status" : "A", 
    "client_id" : ObjectId("5a058e316803fafd127b23c9"), 
    "schools" : [
        ObjectId("5a0e742b6803faa6097b2462"), 
        ObjectId("5a0e742b6803faa6097b2464")
    ]
}
{ 
    "_id" : ObjectId("5a0e76f76803fa530a7b2402"), 
    "pool_name" : "pool B", 
    "pool_status" : "A", 
    "client_id" : ObjectId("5a058e316803fafd127b23c9"), 
    "schools" : [
        ObjectId("5a0e742b6803faa6097b2463")
    ]
}
{ 
    "_id" : ObjectId("5a0e76f76803fa530a7b23f6"), 
    "pool_name" : "pool C", 
    "pool_status" : "A", 
    "client_id" : ObjectId("5a058e316803fafd127b23cb"), 
    "schools" : [
        ObjectId("5a7aa1476803fa1f117b23d1")
    ]
}

学校合集

{ 
    "_id" : ObjectId("5a0e742b6803faa6097b2462"), 
    "school_name" : "School A", 
    "school_status" : "A"
}
{ 
    "_id" : ObjectId("5a0e742b6803faa6097b2463"), 
    "school_name" : "School B", 
    "school_status" : "A"
}
{ 
    "_id" : ObjectId("5a0e742b6803faa6097b2464"), 
    "school_name" : "School C", 
    "school_status" : "A"
}
{ 
    "_id" : ObjectId("5a7aa1476803fa1f117b23d1"), 
    "school_name" : "School D", 
    "school_status" : "A"
}

从这些集合中，我需要为如下所示的客户获取泳池学校列表。

**Output**

{
    "_id" : ObjectId("5a058e316803fafd127b23c9"),
    "client_name" : "client A", 
    "client_status" : "A"
    "pools" : [
        {
            "_id" : ObjectId("5a0e76f66803fa530a7b23d4"), 
            "pool_name" : "pool A", 
            "pool_status" : "A",
            'schools' => [
                { 
                    "_id" : ObjectId("5a0e742b6803faa6097b2462"), 
                    "school_name" : "School A", 
                    "school_status" : "A"
                },
                { 
                    "_id" : ObjectId("5a0e742b6803faa6097b2464"), 
                    "school_name" : "School C", 
                    "school_status" : "A"
                },
            ]
        },
        {
            "_id" : ObjectId("5a0e76f76803fa530a7b2402"), 
            "pool_name" : "pool B", 
            "pool_status" : "A",
            "schools" : [
                { 
                    "_id" : ObjectId("5a0e742b6803faa6097b2463"), 
                    "school_name" : "School B", 
                    "school_status" : "A"
                }   
            ]
        }
    ]
},
{
    "_id" : ObjectId("5a058e316803fafd127b23cb"), 
    "client_name" : "client B",
    "client_status" : "A"
    "pools" : [
        {
            "_id" : ObjectId("5a0e76f76803fa530a7b23f6"), 
            "pool_name" : "pool C", 
            "pool_status" : "A",
            "schools" : [
                { 
                    "_id" : ObjectId("5a7aa1476803fa1f117b23d1"), 
                    "school_name" : "School D", 
                    "school_status" : "A"
                }
            ]
        }
    ]
}

Answer 1

你可以试试下面的聚合

Mongodb 3.6 引入了嵌套 $lookup管道...所以你不需要使用另一个$lookup就像你在上面做的那样......你可以使用嵌套$lookup管道在这里

Client.aggregate([
  { "$match": { 'client_status': 'A' } },
  { "$lookup": {
    "from": Pool.collection.name,
    "let": { "client_id": "$_id" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": [ "$client_id", "$$client_id" ] } } },
      { "$addFields": {
        "schools": {
          "$ifNull": ["$schools", []]
        }
      }}
      { "$lookup": {
        "from": Schools.collection.name,
        "let": { "schools": "$schools" },
        "pipeline": [
          { "$match": { "$expr": { "$in": [ "$_id", "$$schools" ] } } }
        ],
        "as": "schools"
      }}
    ],
    "as": "pools"
  }}
])

对于冗长的解释，你可以通过$lookup multiple levels without $unwind?