How to lookup inside lookup in MongoDB Aggregate?

Question

I have a simple 3 collections. This bellow is their pseudocode. I want to get all shipments and for each shipment, I want to have all bids for that shipment and in each bid, I need userDetails object.

User: {
  name: string,
}

Shipment: {
  from: string,
  to: string
}

Bid: {
  amount: number,
  shipmentId: Ref_to_Shipment
  userId: Ref_to_User
}

This is what I have tried:

const shipments = await ShipmentModel.aggregate([
            {
                $lookup: {
                    from: "bids",
                    localField: "_id",
                    foreignField: "shipmentId",
                    as: "bids"
                }
            },
            {
                $lookup: {
                    from: "users",
                    localField: "bids.userId",
                    foreignField: "_id",
                    as: "bids.user"
                }
            }
        ])

And I got the following result:

[
    {
        "_id": "5fad2fc04458ac156531d1b1",
        "from": "Belgrade",
        "to": "London",
        "__v": 0,
        "bids": {
            "user": [
                {
                    "_id": "5fad2cdb4d19c80d1b6abcb7",
                    "name": "Amel",
                    "email": "Muminovic",
                    "password": "d2d2d2",
                    "__v": 0
                }
            ]
        }
    }
]

I am trying to get all Shipments with their bids and users within bids. Data should look like:

[
    {
        "_id": "5fad2fc04458ac156531d1b1",
        "from": "Belgrade",
        "to": "London",
        "__v": 0,
        "bids": [
            {
                "_id": "5fad341887c2ae1feff73402",
                "amount": 400,
                "userId": "5fad2cdb4d19c80d1b6abcb7",
                "shipmentId": "5fad2fc04458ac156531d1b1",
                "user": {
                    "name": "Amel",
                }
                "__v": 0
            }
        ]
    }
]

Answer 1

Try this

I figured out it based on MongoDB $lookup on array of objects with reference objectId and in the answer from J.F. (data organization). Note that he used id instead of _id

The code is

db.Shipment.aggregate([
  {
    $lookup: {
      from: "Bid",
      localField: "id",
      foreignField: "shipmentId",
      as: "bids"
    }
  },
  {
    $lookup: {
      from: "user",
      localField: "bids.userId",
      foreignField: "id",
      as: "allUsers"
    }
  },
  {
    $set: {
      "bids": {
        $map: {
          input: "$bids",
          in: {
            $mergeObjects: [
              "$$this",
              {
                user: {
                  $arrayElemAt: [
                    "$allUsers",
                    {
                      $indexOfArray: [
                        "$allUsers.id",
                        "$$this.userId"
                      ]
                    }
                  ]
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $unset: [
      "allUsers"
    ]
  },
  // to get just one
  //{
  //    $match: {
  //      "id": 1
  //    }
  //  },
  
])

Answer 2

Try this query and chek if works and the behaviour is as you expected:

db.Shipment.aggregate([
  {
    $lookup: {
      from: "Bid",
      localField: "id",
      foreignField: "shipmentId",
      as: "bids"
    }
  },
  {
    $lookup: {
      from: "user",
      localField: "id",
      foreignField: "id",
      as: "newBids"
    }
  },
  {
    $project: {
      "newBids.id": 0,
      "newBids._id": 0,
      
    }
  },
  {
    $match: {
      "bids.userId": 1
    }
  },
  {
    $addFields: {
      "newBids": {
        "$arrayElemAt": [
          "$newBids",
          0
        ]
      }
    }
  },
  {
    $set: {
      "bids.user": "$newBids"
    }
  },
  {
    $project: {
      "newBids": 0
    }
  }
])

This query do your double $lookup and then a $project to delete the fields you don't want, and look for the userId to add the field user. As $lookup generate an array, is necessary use arrayElemAt to get the first position.

Then $set this value generated into the object as bids.user and remove the old value.

Note that I have used a new field id instead of _id to read easier the data.

Answer 3

Try with the following code:

const shipments = await ShipmentModel.aggregate([
            {
                $lookup: {
                    from: "bids",
                    localField: "_id",
                    foreignField: "shipmentId",
                    as: "bids"
                }
            },
            {
              $unwind: {
                path: "$bids",
                preserveNullAndEmptyArrays: true
              }
            },
            {
                $lookup: {
                    from: "users",
                    localField: "bids.userId",
                    foreignField: "_id",
                    as: "bids.user"
                }
            }
        ])

If you want to prevent null and empty arrays then set

preserveNullAndEmptyArrays: false