Correct usage of $replaceRoot in MongoDB Query

Viewed 50

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 :)

1 Answers

@Joe's answer was correct, thanks :)

Instead of {detail: "$detail"}, try just "$detail"

Related