Mongodb Question posted in
The official documentation can be found here.

MongoDb $lookup through array and aggregate

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 dymanic 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"
    }
  }
])
Tags:

2

Answers


  1. 0

    1. In the $lookup stage, use the $$ prefix to refer to the arraysElemsForlookup variable.

    2. Use $filter operator to filter matching element(s) in the arraysElemsForlookup array. Use $ne operator as upper and outer of $filter operator to check returned result is not an empty array.

    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: {
              $ne: [
                {
                  $filter: {
                    input: "$$arraysElemsForlookup",
                    cond: {
                      $and: [
                        {
                          $eq: [
                            "$orderId",
                            "$$this.orderId"
                          ]
                        },
                        {
                          $eq: [
                            "$consumerId",
                            "$$this.consumerId"
                          ]
                        },
                        {
                          $eq: [
                            "$dispatchId",
                            "$$this.dispatchId"
                          ]
                        }
                      ]
                    }
                  }
                },
                []
              ]
            }
          }
        }
      ],
      as: "lookupData"
    }
  }
])

    Note that you can declaring the data in the arraysElemsForlookup variable in the $lookup stage without the need of $addFields stage that adding new field to all documents.

    {
  $lookup: {
    from: "amounts",
    let: {
      arraysElemsForlookup: [
        {
          "consumerId": "c1",
          "dispatchId": "d1",
          "orderId": 1
        },
        {
          "consumerId": "c5",
          "dispatchId": "d6",
          "orderId": 5
        },
        {
          "consumerId": "c2",
          "dispatchId": "d2",
          "orderId": 2
        }
      ]
    },
    pipeline: [
      {
        $match: {
          $expr: {
            $ne: [
              {
                $filter: {
                  input: "$$arraysElemsForlookup",
                  cond: {
                    $and: [
                      {
                        $eq: [
                          "$orderId",
                          "$$this.orderId"
                        ]
                      },
                      {
                        $eq: [
                          "$consumerId",
                          "$$this.consumerId"
                        ]
                      },
                      {
                        $eq: [
                          "$dispatchId",
                          "$$this.dispatchId"
                        ]
                      }
                    ]
                  }
                }
              },
              []
            ]
          }
        }
      }
    ],
    as: "lookupData"
  }
}

    Demo @ Mongo Playground

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search