MongoDb $lookup through array and aggregate

Question

Below are two collections orders and amounts. I have array of lookup data which is to be added using $addFields in orders aggregate pipeline and iterated and get total amount sum from amounts collections.

db={
  "orders": [
    {
      "_id": 1,
      "shopId": "s1",
      "consumerId": "c1",
      "dispatches": [
        {
          "_id": "d1",
          "status": "assigned",
          "toBeSentOn": ISODate("2024-05-15T10:00:00Z")
        }
      ]
    }
  ],
  "amounts": [
    {
      "_id": "a1",
      "shopId": "s1",
      "orderId": 1,
      "consumerId": "c1",
      "dispatchId": "d1",
      "amount": 100
    }
  ]
}

arraysElemsForlookup is the dynamic array of objects will be added in aggregation pipeline using $addFields and it will contain multiple ids for lookup from another collection.

Here below what I am trying aggregation pipeline but I am not able to access to through loop. as arraysElemsForlookup array of objects are added in aggregation pipeline and I want match consumerId, dispatchId and orderId from amounts collection and show the total sum.

db.orders.aggregate([
  {
    $match: {
      shopId: "s1"
    }
  },
  {
    "$addFields": {
      arraysElemsForlookup: [
        {
          "consumerId": "c1",
          "dispatchId": "d1",
          "orderId": 1
        },
        {
          "consumerId": "c5",
          "dispatchId": "d6",
          "orderId": 5
        },
        {
          "consumerId": "c2",
          "dispatchId": "d2",
          "orderId": 2
        }
      ]
    }
  },
  {
    $lookup: {
      from: "amounts",
      let: {
        arraysElemsForlookup: "$arraysElemsForlookup"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$orderId",
                    "$arraysElemsForlookup.$.$orderId"
                  ]
                },
                {
                  $eq: [
                    "$consumerId",
                    "$arraysElemsForlookup.$.consumerId"
                  ]
                },
                {
                  $eq: [
                    "$dispatchId",
                    "$arraysElemsForlookup.$.dispatchId"
                  ]
                }
              ]
            }
          }
        }
      ],
      as: "lookupData"
    }
  }
])

Answer 1

Mongodb Playground Link: https://mongoplayground.net/p/_hyCAVT2hIb

db.orders.aggregate([
  {
    $match: {
      shopId: "s1"
    }
  },
  {
    "$addFields": {
      arraysElemsForlookup: [
        {
          "consumerId": "c1",
          "dispatchId": "d1",
          "orderId": 1
        },
        {
          "consumerId": "c5",
          "dispatchId": "d6",
          "orderId": 5
        },
        {
          "consumerId": "c2",
          "dispatchId": "d2",
          "orderId": 2
        }
      ]
    }
  },
  {
    $lookup: {
      from: "amounts",
      let: {
        arraysElemsForlookup: "$arraysElemsForlookup"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $anyElementTrue: {
                $map: {
                  input: "$$arraysElemsForlookup",
                  as: "lookup",
                  in: {
                    $and: [
                      {
                        $eq: [
                          "$orderId",
                          "$$lookup.orderId"
                        ]
                      },
                      {
                        $eq: [
                          "$consumerId",
                          "$$lookup.consumerId"
                        ]
                      },
                      {
                        $eq: [
                          "$dispatchId",
                          "$$lookup.dispatchId"
                        ]
                      }
                    ]
                  }
                }
              }
            }
          }
        }
      ],
      as: "lookupData"
    }
  },
  {
    $unwind: {
      path: "$lookupData",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $group: {
      _id: null,
      totalAmount: {
        $sum: "$lookupData.amount"
      }
    }
  },
  {
    $project: {
      _id: 0,
      total: "$totalAmount"
    }
  }
])

$match : This stage filters the documents based on a specific condition.

$addFields : This stage adds a new field to each document in the pipeline.

$lookup : This stage performs a join with another collection".

$anyElementTrue and $map operators : It checks if any element in the array matches the conditions. It takes the array of boolean values produced by $map and returns true if any element in the array is true.

$unwind : This stage opens the array field and creates a separate document for each element in the array.

$group : This stage groups the documents based on a specified key and performs aggregations on the grouped data.

$project : This stage shapes the final output of the aggregation pipeline means what to show and how.