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!
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"
}
},
])