How To Populate objects inside array through aggregation-CodePudding

i got order document with array contains objects, each one contains product id and quantity and also options related to product, what i would to achieve is to join this collection with product collection through aggregation to get certain product data like name, price, image

this is the order document i would like to aggregate

{
_id:ObjectId('61c45d25bdf9c1389879db9f'),
orderItems: [
    {
        options:[
            {
                _id:ObjectId('61fd914d4a236b94f816c27d'),
                question: 'lorem ipsum dolor set amet',
                answer: 'lorem'
            }, {
                _id: ObjectId('61fd914d4a236b94f816c27d'),
                question: 'lorem ipsum dolor set amet',
                answer: 'lorem'
            }, {
                _id: ObjectId('61fd914d4a236b94f816c27d'),
                question: 'lorem ipsum dolor set amet',
                answer: 'lorem'
            }, {
                _id: ObjectId('61fd914d4a236b94f816c27d'),
                question: 'lorem ipsum dolor set amet',
                answer: 'lorem'
            },
        ],
        quantity:4,
        product:ObjectId('61c5e1e7c2ec39450cdf41b7')
    }, {
        options:[
            {
                _id: ObjectId('61fd914d4a236b94f816c27d'),
                question: 'lorem ipsum dolor set amet',
                answer:'lorem'
            }, {
                _id: ObjectId('61fd914d4a236b94f816c27d'),
                question: 'lorem ipsum dolor set amet',
                answer: 'lorem'
            },
            {
                _id: ObjectId('61fd914d4a236b94f816c27d'),
                question: 'lorem ipsum dolor set amet',
                answer: 'lorem'
            }, {
                _id: ObjectId('61fd914d4a236b94f816c27d'),
                question: 'lorem ipsum dolor set amet',
                answer:'lorem'
            },
        ],
        quantity:3,
        product:ObjectId('61c5e1e7c2ec39450cdf41b7')
    }
]}

this is final query I'd like to achieve

{
    _id:ObjectId('61c45d25bdf9c1389879db9f'),
    orderItems: [
        {
            options:[
                {
                    _id: ObjectId('61fd914d4a236b94f816c27d'),
                    question: 'lorem ipsum dolor set amet',
                    answer:'lorem'
                },
                {
                    _id: ObjectId('61fd914d4a236b94f816c27d'),
                    question: 'lorem ipsum dolor set amet',
                    answer:'lorem'
                }, {
                    _id: ObjectId('61fd914d4a236b94f816c27d'),
                    question: 'lorem ipsum dolor set amet',
                    answer: 'lorem'
                }, {
                    _id: ObjectId('61fd914d4a236b94f816c27d'),
                    question: 'lorem ipsum dolor set amet',
                    answer: 'lorem'
            },
        ],
        quantity:2,
        product: {
            name:'lorem ipsum dolor set',
            image:'1452877_product.png',
            type:'immo file',
            price:20
        }
    }, {
        options:[
            {
                _id: ObjectId('61fd914d4a236b94f816c27d'),
                question: 'lorem ipsum dolor set amet',
                answer: 'lorem'
            },
            {
                _id: ObjectId('61fd914d4a236b94f816c27d'),
                question: 'lorem ipsum dolor set amet',
                answer:'lorem'
            }, {
                _id: ObjectId('61fd914d4a236b94f816c27d'),
                question: 'lorem ipsum dolor set amet',
                answer: 'lorem'
            },
            {
                _id: ObjectId('61fd914d4a236b94f816c27d'),
                question: 'lorem ipsum dolor set amet',
                answer: 'lorem'
            },
        ],
        quantity: 3,
        product: {
            name: 'lorem ipsum dolor set',
            image: '1452877_product.png',
            type: 'immo file',
            price: 20
        }
    }
]}

CodePudding user response：

The Solution that worked for me is to unwind the orderItems first then join the products collection using lookup and then use group at last

the code as follow

const orders = await Order.aggregate([
     {
            $unwind:'$orderItems'
        },
        {
            $lookup: {
                from:'products',
                let:{productId:"$orderItems.product"},
                pipeline:[
                    {
                        $match: {
                            $expr:{
                                $eq:["$_id", "$$productId"]
                            }
                        }
                    },
                    {
                        $project:{
                            name:1,
                            price:1,
                            type:1,
                            image:1
                        }
                    }
                ],
                as:"product"
            }
        },
        {
            $set:{"orderItems.product":"$product"}
        },
        {
            $unwind:"$orderItems.product"
        },
        {
            $group:{
                _id:"$_id",
                orderItems:{$push:"$orderItems"}
            }
        }
])