CODEWITHSUNDEEP
mongodbmongoosemongodb-queryaggregation-framework
hari prasanth
hari prasanth

Reputation: 746

How to join two collection result and another collection using mongodb

I tried join two collections output and one collections.but its not working how to join two collections results join to one coleection using mongodb

promotion collection 

[{
    "id":1,
    "name":"latest",
    "product":[{
                 "id":3,
              }]

}]

product collection 

[{
     "id":3,
     "product_name":"bourbon",
     "category_id": 18
}]

Category Collection 

[{
   "id":10,
   "name":"laptop"
}]

Mapping Code 

db.promotion.aggregate([{$lookup:{
            from:'product',
            localField:'products',
            foreignField:'id',
            as:'products'
        }}]).toArray()

I Got Output 

[{
    "id":1,
    "name":"latest",
    "product":[{
                 "id":3,
                  "product_name":"bourbon",
                  "category_id": 18
              }]

}]

Excepted output 

[{
    "id":1,
    "name":"latest",
    "product":[{
                 "id":3,
                  "product_name":"bourbon",
                  "name":"laptop"
              }]

}]

How to achieve it.this scenario

Upvotes: 1

Views: 134

Answers (1)

Ashh
Ashh

Reputation: 46451

You can use below aggregation

db.promotion.aggregate([
  { "$lookup": {
    "from": "product",
    "let": { "products": "$products" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$id", "$$products"] } } },
      { "$lookup": {
        "from": "category",
        "let": { "category_id": "$category_id" },
        "pipeline": [
          { "$match": { "$expr": { "$eq": ["$id", "$$category_id"] } } }
        ],
        "as": "category"
      }},
      { "$project": {
        "id": 1,
        "product_name": "bourbon",
        "name": { "$arrayElemAt": ["$category.name", 0] }
      }}
    ],
    "as": "product"
  }}
])

Upvotes: 1

Related Questions