MongoDB aggregation lookup pipeline query

Question

I'm looking for help with a MongoDB lookup aggregation query.

I'm trying to join two collections, namely "cafe" and "stamp". During the lookup, I would like to match stamps for a given cafe and then further sort and limit the results by 10.

The correlated id fields are: "_id" for the cafe collection and "cafeId" in for the stamp collection.

The following is what I have come up with, however there seems to be a problem when trying to match by ids between the given collections. I believe this may have something to do with Mongo not treating them as ObjectID's, but I'm unsure.

db.cafe.aggregate([
      {
        $lookup: {
          from: "stamp",
          as: "stamps",
          let: {
            id: "$_id",
            name: "$name"
          },
          pipeline: [
            { $project: { id:1, cafeId: { $toObjectId: "$$id"}, name:1 } },
            { $match: { expr: { $eq: ["$$cafeId", "$cafeId"] } } },
            { $sort: { stampDate: -1 } },
            { $limit: 10 }
          ]
        }
      }
    ]);

Eladian Eladian · Accepted Answer · 2020-09-29T04:27:57

Managed to figure out how to achieve what I wanted. Leave this here for anyone else that is trying to achieve the same results.

db.cafe.aggregate([
  {
    $lookup: {
      from: "stamp",
      as: "stamps",
      let: {
        id: "$_id",
        name: "$name" //All cafes variables,
      },
      pipeline: [
        {
          $project: {
            cafeId: 1,
            stampDate: 1,
            stampId: 1,
            cafeId: 1,
            status: 1,
            userId: { $toObjectId: "$userId" }
          }
        },
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [{ $toObjectId: "$$id" }, { $toObjectId: "$cafeId" }]
                },
                {
                  $eq: [
                    { $toObjectId: "$userId" },
                    mongoose.Types.ObjectId(req.user._id)
                  ]
                }
              ]
            }
          }
        }
      ]
    }
  }
]);

MongoDB aggregation lookup pipeline query

1 Answers