I got a MongoDB instance with 3 tables built in the following way:
- Company {_id, [productIds], foo, bar, ...}
- Product {_id, productCategoryId, foo, bar, ...}
- ProductCategory {_id, foo, bar, ...}
I want a result that returns all company data with an additional field. That should contain all product data (so basically a join on the productIds). Each Product should be joined with its category.
I got the following query for this:
db.companyentities.aggregate([
{
$unwind: "$productIds"
},
{
$lookup: {
as: "products",
foreignField: "_id",
from: "productentities",
localField: "productIds"
}
},
{
$unwind: "$products"
},
{
$lookup: {
as: "products.productCategory",
foreignField: "_id",
from: "productcategoryentities",
localField: "products.productCategoryId"
}
},
{
$unwind: "$products.productCategory"
},
{
$group: {
_id: "$_id",
products: {
$push: "$products"
},
detail: {
$first: "$$ROOT"
}
}
},
{
$replaceRoot: {
newRoot: {
$mergeObjects: [
{products: "$products"},
{detail: "$detail"}
],
}
}
}
])
I got the joins right, the problem is that the result has the format {company id, details: {companyData}, products { productData }}.
What I want is the company data as is in the Database, but with an extra column that contains the product data.
How do I achieve this? I guess I'm doing sth wrong on the $replaceRoot part.
Thanks in advance for any suggestions :)