使用 MongoDB $lookup 对包含引用 objectId 的对象数组进行关联查询

Question

我有一个Orders集合，我正在从中获取数据，如下所示：

 [
  {
    "_id": "628216b7b30bb8aa80c8fd1a",
    "promotionsDetails": {
      "companyTotalPrice": 27,
      "promotionsData": [
        {
          "_id": "621de063bb5f9f0bf510897f",
          "price": 27,
          "companyId": "621dd85eb45ca2ae292d9a36"
        },
        {
          "_id": "621de063bb5f9f0bf510897d",
          "price": 19,
          "companyId": "621dd85eb45ca2ae292d9a32"
        }
      ]
    }
  },
  {
    "_id": "628214fcb30bb8aa80c8fd18",
    "promotionsDetails": {
      "companyTotalPrice": 46,
      "promotionsData": [
        {
          "_id": "621de063bb5f9f0bf510897f",
          "price": 46,
          "companyId": "621dd85eb45ca2ae292d9a32",
        }
      ]
    },
  }
]

我想要做的是从公司集合中获取每个对象中使用的companyId objectId来获取公司详情，就像下面这样：

    [
  {
    "_id": "628216b7b30bb8aa80c8fd1a",
    "promotionsDetails": {
      "companyTotalPrice": 27,
      "promotionsData": [
        {
          "_id": "621de063bb5f9f0bf510897f",
          "price": 27,
          "companyId": "621dd85eb45ca2ae292d9a36",
          "companyData": { "title": "..." }
        },
        {
          "_id": "621de063bb5f9f0bf510897d",
          "price": 19,
          "companyId": "621dd85eb45ca2ae292d9a32",
          "companyData": { "title": "..." }
        }
      ]
    }
  },
  {
    "_id": "628214fcb30bb8aa80c8fd18",
    "promotionsDetails": {
      "companyTotalPrice": 46,
      "promotionsData": [
        {
          "_id": "621de063bb5f9f0bf510897f",
          "price": 46,
          "companyId": "621dd85eb45ca2ae292d9a32",
          "companyData": { "title": "..." }
        }
      ]
    }
  }
]

我尝试使用查找和管道，但是没有得到期望的结果，谢谢！

- Reda Khalaf

Answer 1

实际上，$lookup支持数组，因此没有必要进行$unwind操作并更改结构。这将返回您期望的结果：

db.Orders.aggregate([
  {
    $lookup: {
      from: "Company",
      localField: "promotionsDetails.promotionsData.companyId",
      foreignField: "_id",
      as: "companyfullData"
    }
  },
  {
    $set: {
      "promotionsDetails.promotionsData": {
        $map: {
          input: "$promotionsDetails.promotionsData",
          in: {
            $mergeObjects: [
              "$$this",
              {
                companyData: {
                  $arrayElemAt: [
                    "$companyfullData",
                    {$indexOfArray: ["$companyfullData.id", "$$this.id"]}
                  ]
                }
              }
            ]
          }
        }
      }
    }
  },
  {$unset: "companyfullData"}
])

Playground

- nimrod serok

---

实际上它是可以工作的，但它没有将公司数据填充到companyData对象中，而是将数据填充到promotionsData数组项中，并覆盖任何具有相同名称的现有字段。 - Reda Khalaf

已更新为插入到 companyData。 - nimrod serok

尼姆罗德，谢谢你的解决方案，正是我所寻找的。你非常接近了，只是$indexOfArray中有一个小错误。$companyfullData.id应该匹配$$this.companyId，而不是$$this.id，这是促销ID。我的修复$indexOfArray: ["$companyfullData._id", "$$this.companyId"]。https://mongoplayground.net/p/Myci5IHuy8S - undefined

Answer 2

这里，为了更清晰明了，请查看Mongo playground。

db.Orders.aggregate([
  {
    $unwind: "$promotionsDetails.promotionsData"
  },
  {
    "$lookup": {
      "from": "Company",
      "localField": "promotionsDetails.promotionsData.companyId",
      "foreignField": "_id",
      "as": "promotionsDetails.promotionsData.companyData"
    }
  },
  
])

- nedoder

---

正如Nimrod所说，是的，它正在改变对象的结构，但对我来说无关紧要。感谢您的回答！ - Reda Khalaf

如果需要，您可以使用$group来重新安排结构。不用谢 :) - nedoder