$match query problem using aggregation with mongodb

Question

I am trying to do a very simple query joining two collections using aggregation.

I have two collections,

productSchema = new Schema({
    product: { type: String },
    price: { type: Number},
})

const inventoriesSchema= new Schema({
    date: { type: Date},
    products: [productSchema],
})

Now i want to bring all the products and inside of every product i want to bring the inventory.

This is what i am doing.

    productModel.aggregate([
        {
            $lookup: {
                from: 'inventories',
                let: {
                    'product': { $toString: '$_id' }
                },
                pipeline: [
                    {
                        $match: {
                            'products._id': '$$product'
                        }
                    },
                ],
                as: 'inventory',
            }
        },
      ])

i tried a lot of ways... with $toObjectId $toString... using $expr also like this...

                     {
                        $match: {                              
                            $expr: {
                                $and: [
                                    { $eq: ['$product._id', { $toString: "$$product" }] }
                                ]

                            }
                        }
                    }

What i am doing wrong... ???

What is the relation between products & inventories collection which field has to be used to JOIN ? Does product field is what we need to look in inventories collection products array ? Please provide sample docs & required o/p.. — whoami - fakeFaceTrueSoul
– whoami - fakeFaceTrueSoul, Commented Apr 30, 2020 at 16:31

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-04-30 16:35:12Z

1

Try below aggregation query which uses $in to check whether an expression exists in array or not :

Assuming your product field in products Coll is key to join & needs to be checked in products array of inventories Coll.

productModel.aggregate([
    {
        $lookup: {
            from: 'inventories',
            let: { 'product': '$product' }, // create local variable for products
            pipeline: [
                { $match: { $expr: { $in: [ "$$product",  "$products" ] } } } // check product from products Coll exists in products of inventories Coll
            ],
            as: 'inventory'
        }
    }
  ])

answered Apr 30, 2020 at 16:35

whoami - fakeFaceTrueSoul

18.1k6 gold badges35 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sergio Cano Over a year ago

uhh.... i was missing $in to check the _id inside the array... i was just trying to $match the inventories that contains the product inside... thanks for your quick answer

Collectives™ on Stack Overflow

$match query problem using aggregation with mongodb

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related