如何使用 mongodb 连接两个集合结果和另一个集合

Question

我尝试连接两个集合输出和一个集合.但它不起作用 如何使用 mongodb 连接两个集合结果连接到一个集合

促销收集

[{
    "id":1,
    "name":"latest",
    "product":[{
                 "id":3,
              }]
}]

产品集合

[{
     "id":3,
     "product_name":"bourbon",
     "category_id": 18
}]

类别集合

[{
   "id":10,
   "name":"laptop"
}]

映射代码

db.promotion.aggregate([{$lookup:{
            from:'product',
            localField:'products',
            foreignField:'id',
            as:'products'
        }}]).toArray()

我得到了输出

[{
    "id":1,
    "name":"latest",
    "product":[{
                 "id":3,
                  "product_name":"bourbon",
                  "category_id": 18
              }]
}]

例外输出

[{
    "id":1,
    "name":"latest",
    "product":[{
                 "id":3,
                  "product_name":"bourbon",
                  "name":"laptop"
              }]
}]

如何实现它。此方案

Answer 1

您可以使用

以下聚合

db.promotion.aggregate([
  { "$lookup": {
    "from": "product",
    "let": { "products": "$products" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$id", "$$products"] } } },
      { "$lookup": {
        "from": "category",
        "let": { "category_id": "$category_id" },
        "pipeline": [
          { "$match": { "$expr": { "$eq": ["$id", "$$category_id"] } } }
        ],
        "as": "category"
      }},
      { "$project": {
        "id": 1,
        "product_name": "bourbon",
        "name": { "$arrayElemAt": ["$category.name", 0] }
      }}
    ],
    "as": "product"
  }}
])