Lookup inside nested array object

I have the nested array object and I need to lookup the field inside the array object.

Data structure:

- loads (collection)
     - orders (collection)
         - costs (sub document)[
              {
                cost_centre: ( object Id of costcentres collection)
              }
            ]

I tried this query

Query:

db.loads.aggregate([
        {
            $lookup: {
                from: "orders",
                localField: "order",
                foreignField: "_id",            
                as: "load_order",
                pipeline:[{

                  $lookup: {
                      from: "costcentres",
                      localField: "load_order.costs.cost_centre",
                      foreignField: "_id",
                      as: "_cost_centre",
                  }
             }]
               
            }
        },
])

But this is not populating ‘cost_centre’, can someone help me ?

I am pretty sure that you cannot refer to load_order inside the inner $lookup because the field is being created by the outer one. Technically, it does not exist yet.

Try to remove pipeline: from the outer $lookup and move the inner one as another stage of the main pipeline. Something like the following untested code:

db.loads.aggregate( [
        { $lookup: {
                from: "orders",
                localField: "order",
                foreignField: "_id",            
                as: "load_order"               
         } } ,
        { $lookup: {
                from: "costcentres",
                localField: "load_order.costs.cost_centre",
                foreignField: "_id",
                as: "_cost_centre",
         } }
] )

In principal, you do not need to $unwind before using $lookup on an array.

Note that there will be no duplicate in _cost_center even if some order refers to the same cost_center.

The array _cost_center will be a top level field just like load_order, so cost_center documents will not be a sub-document of load_order. You might want to use $addField with $map if you want to embed cost_center into load_order. Personally, I prefer to perform this kind of data cosmetic on the application. Doing the $addField/$map will increased the data transmitted if some load_order refers to the same cost_center.