how to merge multiple collections in mongodb node js using mongodb driver

Question

I have 3 collections. i want to combine those and filter data from merged data.

Business Collection

{
    
    _id:1,
    "user_id": 1,
    "name": "Doll Shopqq",
    "registered_phone_number": 701006522222109,   
    "business_profile_image_url": "http://website.com/hiyup_dev/business/1611569489867_businessImage.jpeg",
    "email": "",
    "media_urls": ["http://website.com/hiyup_dev/business/1611569503298_3176405500.jpeg",
    "http://website.com/hiyup_dev/business/1611569983527_3192836205.mp4"],
    "description": "Doll shop",
    "products": [{ 
        "_id": 1
        "name": "Dog Biscuits",
        "lower_price": "0.00",
        "media_urls": ["http://website.com/hiyup_dev/product/1611569983527_3192836205.jpeg", "http://website.com/hiyup_dev/product/1611569983527_3192836205.mp4"],
        "higher_price": "0.00",
        "description": "Biscuits",
    }, {
        "_id": 2,
        "name": "Dog Biscuits-1",
        "lower_price": "0.00",
        "media_urls": ["http://website.com/hiyup_dev/product/1611569983527_3192836205.jpeg"],
        "higher_price": "0.00",
        "description": "Biscuits-1",
    }],
    "status": 1,
    "country_code": ""
}

Offer Collection

{ 
    "_id": 1,
    "name": "offer name 1",
"user_id": 1,
    "lower_price": "0.00",
    "media_urls": ["http://website.com/hiyup_dev/offer/1611569983527_3192836205.jpeg",
    "http://website.com/hiyup_dev/offer/1611569983527_3192836205.mp4"],
    "higher_price": "0.00",
    "description": "Biscuits",
}, {
    "_id": 2,
    "name": "offr name2", "user_id": 1,
    "lower_price": "0.00",
    "media_urls": ["http://website.com/hiyup_dev/offer/1611569983527_3192836205.jpeg"],
    "higher_price": "0.00",
    "description": "Biscuits-1",
}

Product Request Collection

   { 
    "_id": 1,
    "name": "request  name 1", "user_id": 1,
    "lower_price": "0.00",
    "media_urls": ["http://website.com/hiyup_dev/request/1611569983527_3192836205.jpeg",
    "http://website.com/hiyup_dev/request/1611569983527_3192836205.mp4"],
    "higher_price": "0.00",
    "description": "Biscuits",
}, {
    "_id": 2,
    "name": "request name2", "user_id": 1,
    "lower_price": "0.00",
    "media_urls": ["http://website.com/hiyup_dev/product/1611569983527_3192836205.jpeg"],
    "higher_price": "0.00",
    "description": "Biscuits-1",
}

From business collection i need to fetch the products which has video in products.media_urls likewise from offer, product_request collection i want to fetch the item which has video in media_urls. I want to fetch the items from product, offers, product_request which has video in their media_url array.

I want to combine these collections and filter the media_urls which has only video. For a Single collection i done filtering using regex.

But i couldnt combine multiple collections. when i used unwind. duplicate data are coming.

My expected out put is

{
    "_id": 2, //or some other key name like product_id
    **"type": "products"**
    "name": "Dog Biscuits-1",
    "lower_price": "0.00",
    "media_urls": [
    "http://website.com/hiyup_dev/product/1611569983527_3192836205.mp4"],
    "higher_price": "0.00",
     "description": "Biscuits-1",
},
{
    "_id": 1,//or some other key name
    "type": "offer"
    "name": "offer name 1",
    "lower_price": "0.00",
    "media_urls": [
    "http://website.com/hiyup_dev/offer/1611569983527_3192836205.mp4"],
    "higher_price": "0.00",
    "description": "Biscuits",
},
{
    "_id": 1,//or some other key name
    "type": "request"
    "name": "request  name 1",
    "lower_price": "0.00",
    "media_urls": [
    "http://website.com/hiyup_dev/request/1611569983527_3192836205.mp4"],
    "higher_price": "0.00",
    "description": "Biscuits",
},
{
    "_id": 1,//or some other key name
    "type": "business"
    "media_urls": [
    "http://website.com/hiyup_dev/business/1611569983527_3192836205.mp4"],
}

Answer 1

db.businessreq.aggregate(
{
    $lookup: {
        from: 'businessreq', pipeline: [
            { $unwind: { path: "$products", preserveNullAndEmptyArrays: true } },
            { $unwind: { path: "$products.media_urls", preserveNullAndEmptyArrays: true } },
            { $match: { "products.media_urls": { $regex: ".mp4", $options: "$i" } } },
            { $addFields: { "products.type": "product" } }
        ],
        as: 'breq'
    }
},
{
    $lookup: {
        from: 'offer', pipeline: [
            { $unwind: { path: "$media_urls", preserveNullAndEmptyArrays: true } },
            { $match: { "media_urls": { $regex: ".mp4", $options: "$i" } } },
            { $addFields: { "type": "offer" } }
        ],
        as: 'off'
    }
},
{
    $lookup: {
        from: 'productRequest', pipeline: [
            { $unwind: { path: "$media_urls", preserveNullAndEmptyArrays: true } },
            { $match: { "media_urls": { $regex: ".mp4", $options: "$i" } } },
            { $addFields: { "type": "request" } }
        ],
        as: 'prodReq'
    }
},
{
    $lookup: {
        from: 'businessreq', pipeline: [
            { $unwind: { path: "$media_urls", preserveNullAndEmptyArrays: true } },
            { $match: { "media_urls": { $regex: ".mp4", $options: "$i" } } },
            { $addFields: { "type": "business" } }
        ],
        as: 'buiReq'
    }
},
{
    "$project":
    {
        "Union": { $concatArrays: ["$breq.products", "$off", "$prodReq", "$buiReq"] }
    }
},
{ $unwind: "$Union" },
{ $replaceRoot: { newRoot: "$Union" } },
{
    "$project": {
        products: 0
    }
}

).pretty();