如何在两个不同的集合中对数组使用$ lookup？

Question

这是“公司”收集数据

{ 
    _id: "1", 
    company: "ABC", 
    addresses: [ 
    { 
     _id: "2", 
     address: "a1", 
     district: "d1", 
     city: "c1" 
    }, 
    { 
     _id: "3", 
     address: "a2", 
     district: "d2", 
     city: "c2" 
    }, 
    { 
     _id: "4", 
     address: "a3", 
     district: "d3", 
     city: "c3" 
    } 
    ] 
} 

这是“产品”收集数据

{ 
    product: "xyz", 
    companyid: "1",   //_id of record in company's collection 
    addresses: ["2", "4"] //addresses _id of record in company's collection 
}, 
{ 
    product: "pqr", 
    companyid: "1", 
    addresses: ["3", "4"] 
} 

现在我需要与公司所有产品及其地址，像下面

{ 
    product: "xyz", 
    company: "ABC", 
    addresses: [ 
    { 
     _id: "2", 
     address: "a1", 
     district: "d1", 
     city: "c1" 
    }, 
    { 
     _id: "4", 
     address: "a3", 
     district: "d3", 
     city: "c3" 
    } 
    ] 
}, 
{ 
    product: "pqr", 
    companyid: "ABC", 
    addresses: [ 
    { 
     _id: "3", 
     address: "a2", 
     district: "d2", 
     city: "c2" 
    }, 
    { 
     _id: "4", 
     address: "a3", 
     district: "d3", 
     city: "c3" 
    } 
    ] 
} 

如果有数组，只有在产品中，我们可以放开它们并执行查找。但是在这种情况下，两个集合中都有数组，那么我们如何比较它们或使用$ lookup来获得预期的输出？如果有其他选择，请告诉我。

Answer 1

你可以尝试运行下面的总操作以获取所需的结果：

db.product.aggregate([ 
    { 
     "$lookup": { 
      "from": "company", 
      "localField": "companyid", 
      "foreignField": "_id", 
      "as": "company" 
     } 
    }, 
    { 
     "$addFields": { 
      "company": { "$arrayElemAt": ["$company", 0] } 
     } 
    }, 
    { 
     "$addFields": {    
      "addresses": { 
       "$filter": { 
        "input": "$company.addresses", 
        "as": "ad", 
        "cond": { 
         "$setIsSubset": [ 
          ["$$ad._id"], 
          "$addresses" 
         ] 
        } 
       } 
      } 
     } 
    }, 
    { "$project": { "company": 0 } } 
])