How to combine nested $lookup 3 level in mongoose?

Question

I want to combine separate collections to 1 json, but I have some problems in nested $lookup.

This is my example collections. Collection Package_subscriptions:

[
    {
        "_id": "1",
        "package_name": "Small",
        "package_desc": "AAA",
        "package_price": 10
    },
    {
        "_id": "2",
        "package_name": "Medium",
        "package_desc": "BBB",
        "package_price": 20    
    }

Collection Package_modules:

{
    "_id" : 1,
    "subscription_id" : 1,
    "billing_model_id" : 1,
    "module_id" : 1,
    "created_at" : ISODate("2019-06-17T07:59:43.199Z"),
}
{
    "_id" : 2,
    "subscription_id" : 1,
    "billing_model_id" : 3,
    "module_id" : 2,
    "created_at" : ISODate("2019-06-17T08:00:37.464Z"),
}
{
    "_id" : 3,
    "subscription_id" : 2,
    "billing_model_id" : 2,
    "module_id" : 1,
    "created_at" : ISODate("2019-06-17T08:00:56.610Z"),
}
{
    "_id" : 4,
    "subscription_id" : 2,
    "billing_model_id" : 4,
    "module_id" : 2,
    "created_at" : ISODate("2019-06-17T08:01:29.667Z"),
}

Collection Modules:

{
    "_id" : 1,
    "module_name" : "Call",
    "status" : "Active",
}
{
    "_id" : 2,
    "module_name" : "SMS",
    "status" : "Active",
}

Collection Billing_models

{
    "_id" : 1,
    "unit_count" : "Menit",
    "counter" : 2000,
},
{
    "_id" : 2,
    "unit_count" : "Menit",
    "counter" : 3000,
}
{
    "_id" : 3,
    "unit_count" : "SMS",
    "counter" : 3000,
},
{
    "_id" : 4,
    "unit_count" : "SMS",
    "counter" : 5000,
}

This is my code to try the issue, but there's not as expected.

Package_subscription
            .aggregate([
                {
                    $lookup: {
                            from: "o_package_modules",
                            localField: "_id",
                            foreignField: "subscription_id",
                            as: "package_modules"
                    }
                },
                {
                    $unwind: {
                        path: "$package_modules",
                        preserveNullAndEmptyArrays: true                        
                    }
                },
                {
                    $lookup: {
                        from: "o_modules",
                        localField: "package_modules.module_id",
                        foreignField: "_id",
                        as: 'module'
                    }
                },
                {
                    $lookup: {
                        from: "o_billing_models",
                        localField: "package_modules.billing_model_id",
                        foreignField: "_id",
                        as: 'billing_module'
                    }
                }
            ])            
            .exec()                        
            .then((pricing) => {                
                res.json(pricing)                
            })
            .catch((err) => {
                res.send(err)
            })

I expect the output is:

[
    {
        "_id": "1",
        "package_name": "Small",
        "package_desc": "AAA",
        "package_price": 10,        
        "package_modules": [
            {
                "_id": 1,
                "subscription_id": 1,
                "billing_model": {
                        "_id": 1,
                        "unit_count": "Menit",
                        "counter": 2000
                    },
                "module": {
                        "_id": 1,
                        "module_name": "Call",
                        "status": "Active"
                    },
                "created_at": "2019-06-17T07:59:43.199Z",
            },
            {
                "_id": 2,
                "subscription_id": 1,
                "billing_model": {
                        "_id": 3,
                        "unit_count": "SMS",
                        "counter": 3000
                    },
                "module": {
                        "_id": 2,
                        "module_name": "SMS",
                        "status": "Active"
                    },
                "created_at": "2019-06-17T08:00:37.464Z",
            }
        ]
    },
    {
        "_id": 2,
        "package_name": "Medium",
        "package_desc": "BBB",
        "package_price": 20,
        "package_modules": [
            {
                "_id": 3,
                "subscription_id": 2,
                "billing_model": {
                        "_id": 2,
                        "unit_count": "Menit",
                        "counter": 3000
                    },
                "module": {
                        "_id": "1",
                        "module_name": "Call",
                        "status": "Active"
                    },
                "created_at": "2019-06-17T08:01:29.667Z",
            },
            {
                "_id": 4,
                "subscription_id": 2,
                "billing_model": {
                        "_id": 4,
                        "unit_count": "SMS",
                        "counter": 5000
                    },
                "module": {
                        "_id": 2,
                        "module_name": "SMS",
                        "status": "Active"
                    },
                "created_at": "2019-06-17T08:01:50.285Z",
            }
        ]
    }
]

But the output from my code is:

[
    {
        "_id": "1",
        "package_name": "Small",
        "package_desc": "AAA",
        "package_price": 10,        
        "package_modules": {
                "_id": 1,
                "subscription_id": 1,
                "billing_model_id": 1
                "module_id": 1                 
                "created_at": "2019-06-17T07:59:43.199Z",
            },            
        },
        "billing_model": [
            {
                "_id": 1,
                "unit_count": "Menit",
                "counter": 2000
            },
        ],

        "module": [
            {
                "_id": 1,
                "module_name": "Call",
                "status": "Active"
            }
        ]
    },
    {
        "_id": "1",
        "package_name": "Small",
        "package_desc": "AAA",
        "package_price": 10,        
        "package_modules": {
                "_id": 2,
                "subscription_id": 2,
                "billing_model_id": 3
                "module_id": 2                 
                "created_at": "2019-06-17T07:59:43.199Z",
            },            
        },
        "billing_model": [
            {
                "_id": 3,
                "unit_count": "SMS",
                "counter": 3000
            },
        ],

        "module": [
            {
                "_id": 2,
                "module_name": "SMS",
                "status": "Active"
            }
        ]
    }
    ..........
    // And medium where is loop again
]

Answer 1

thank you its working for me

var order_details_data = await OrderDetails.aggregate([{
  '$match': {
    `order_id`: ObjectId(val._id)
  }
}, {
  $sort: {
    date: -1
  }
}, {
  $addFields: {
    o_price: {
      $toDouble: `$o_price`
    }
  }
}, {
  $lookup: {
    from: `products`,
    localField: `product_id`,
    foreignField: `_id`,
    as: `product_id`,
  },
}, {
  $unwind: {
    path: `$product_id`,
    preserveNullAndEmptyArrays: true
  }
}, {
  $lookup: {
    from: `productvarientvalues`,
    localField: `product_id.material_id`,
    foreignField: `_id`,
    as: `product_id.material_id`,
  },
}, {
  $unwind: {
    path: `$product_id.material_id`,
    preserveNullAndEmptyArrays: true
  }
}, {
  $group: {
    _id: `$order_id`,
    date: {
      $first: `$date`
    },
    totalAmount: {
      $sum: `$totalAmount`
    },
    orderDetails: {
      $push: `$$ROOT`
    },
    delivery_address: {
      $first: delivery_address
    },
    ship_address_id: {
      $first: ship_address_id
    },
    //  material_id: { $push: `$product_id.material_id` }
    product_id: {
      $push: `$product_id`
    },
  }
}, {
  $project: {
    // test: `$test`,
    totalAmount: {
      $multiply: [`$qty`, `$o_price`]
    },
    o_price: `$o_price`,
    product_id: `$product_id`,
    order_id: `$order_id`,
    user_id: `$user_id`,
    product_id: {
      $arrayElemAt: [`$product_id`, 0]
    },
    data: {
      $arrayElemAt: [`$data`, 0]
    },
    qty: `$qty`,
    date: {
      $dateToString: {
        format: `%M `,
        date: `$date`
      }
    },
    discount: `$discount`,
    examTotal: {
      $sum: [`$qty`, `$price`]
    },
    createdAt: {
      $dateToString: {
        format: `%Y-%m-%d %H:%M`,
        date: `$createdAt`
      }
    },
  }
}

Answer 2

I think there are multiple issue in your code.

1. In your $lookup stage, you need to setup the as field as the package_modules.billing_module and package_modules.module instead of billing_module and module, and so on, so that it becomes a field of package_module object instead of a separate object itself.

2. you need to unwind after each $lookup stage, as $lookup returns an array instead of an object.

3. You need to $group at the end of the aggregation pipeline to push all the package_modules of one subscription into one array.

After resolving all the above issues, Your aggregation pipe should look like this:

Package_subscription
        .aggregate([
            {
                $lookup: {
                        from: "o_package_modules",
                        localField: "_id",
                        foreignField: "subscription_id",
                        as: "package_modules"
                }
            },
            {
                $unwind: {
                    path: "$package_modules",
                    preserveNullAndEmptyArrays: true                        
                }
            },
            {
                $lookup: {
                    from: "o_modules",
                    localField: "package_modules.module_id",
                    foreignField: "_id",
                    as: 'package_modules.module'
                }
            },
            {
                $unwind: {
                    path: "$package_modules.module",
                    preserveNullAndEmptyArrays: true                        
                }
            },
            {
                $lookup: {
                    from: "o_billing_models",
                    localField: "package_modules.billing_model_id",
                    foreignField: "_id",
                    as: 'package_modules.billing_module'
                }
            },
            {
                $unwind: {
                    path: "$package_modules.billing_module",
                    preserveNullAndEmptyArrays: true                        
                }
            },
            {
                $group: {
                    _id: "$_id",
                    package_modules : {$push : "$package_modules"},
                    package_name: {$first . : "$package_name"},
                    package_desc: {$first . : "$package_desc"},
                    package_price: {$first . : "$package_price"}
                }
            }
        ])            
        .exec()                        
        .then((pricing) => {                
            res.json(pricing)                
        })
        .catch((err) => {
            res.send(err)
        })

The $push in the last $group stage will combine all package_modules into one array. And i think that is what you want at the end.

I hope this works for you.