Lookup inside nested array object

Working with Data
1

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 ?

2

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.

3

Hi @steevej,

I have the other problem.

{
  '$lookup': {
      'from': 'comments', // this should be your collection name for candidates.
      'let': {'id_field': {$toObjectId: "$_id"}},
      pipeline: [
          {
              $match: {
                  $expr: {
                      $and: [
                          {$eq: ["$post", "$id_field"]},
                      ]
                  }
              }
          },
          {$sort: {createdAt: -1}},
          {$limit: 5},
          {
              '$lookup': {
                  'from': 'likes', // this should be your collection name for candidates.
                  'let': {'id_field': {$toObjectId: "$_id"}},
                  pipeline: [
                      {
                          $match: {
                              $expr: {
                                  $and: [
                                      {$eq: ["$liker", mongoose.Types.ObjectId(userId)]},
                                      {$eq: ["$comment", "$id_field"]},
                                  ]
                              }
                          }
                      },
                  ],
                  'as': 'likes_u'
              }
          },
      ],
      'as': 'comments'
  }
}

this is the nested lookup.
But comments have the value, likes_us is always empty.

4

If you read the documentation about let in lookup you will see that your variable id_field should use $$id_field with 2 dollar signs rather than $id_field with a single $ sign.

Having to continuously convert data with $toObjectId is detrimental to performance. ObjectId should be stored as ObjectId rather than the string representation. ObjectId is smaller and faster and way faster if you do not need to convert each and every one when you are querying.

This thread is 7 months old. You should start a new thread with more details about your issue. You should include sample documents from your source collections. Expected results based on your sample documents are also needed.