MongoDB $lookup on array of objects with reference objectId-CodePudding

I have Orders collection and iam getting the data from it as shown below:

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

what I am trying to do is to get the company details from the companies collection using the companyId objectId in each object in the array, like below:

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

i have tried to use lookup and pipeline, but I'm not getting the desired result, thanks!

CodePudding user response：

Here, to make it more clear take a look at Mongo playground

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

CodePudding user response：

Actuality $lookup supports arrays, so there is no need to $unwind and change the structure. This will return your expected results:

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