0

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"],
}
Joyner
  • 312
  • 1
  • 2
  • 11
  • how do you want to combine this collections? is there any condition for combine collection? – turivishal May 03 '21 at 15:55
  • i have a user_id field in all collection. But i dont want to combine with the help of user_id. i want to extract all videos(media_url) from product, offer, product_requests – Joyner May 03 '21 at 17:24
  • what you want to select from business collection? there is a root level media_url field and also in array products. – turivishal May 03 '21 at 17:40
  • 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_url. I want to fetch the items from product, offers, product_request which has video in their media_url array. – Joyner May 03 '21 at 18:28
  • how to identify its video url or other? is there any specific video extensions? – turivishal May 03 '21 at 18:30
  • yes it has only .mp4 extension – Joyner May 04 '21 at 05:14

1 Answers1

2
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();

sudha sabarinathan
  • 36
  • 1