Appointment sample data

{
        "doctorId": "623f484709fb21a7760ce187",
        "userId": "6223370153c8126cd85884ce",
        "forUser": {
            "whom": "other",
            "id": "626249c4b666dc59628502f2"
        },
        "appointmentType": "walkin",
        "dates": {
            "createdAt": "2022-04-30T08:06:30.066Z",
            "forDateAndShift": {
                "date": "2022-05-02T00:00:00.000Z",
                "shift": "623f484709fb21a7760ce188"
            }
        },
        "status": "confirmed",
        "_id": "626cee063558d4a281fcccdb",
    }

Doctor Sample Data

"name": "Someone",
    "profile": {
        "qualification": "MBBS",
        "speciality": "Cardiologist",
        "experience": 5
    },
    "timeSlots": [
        {
            "day": "3",
            "shifts": [
                {
                     _id:"623f484709fb21a7760ce188"
                    "maximumAllowedAppointments": 30,
                    "startTime": {
                        "hours": "9",
                        "minutes": "0"
                    },
                    "endTime": {
                        "hours": "12",
                        "minutes": "0"
                    }
                },
            ]
        },

Want to get doctor shift timing that matches the appointment shift id tried that using the pipeline and let inside lookup and that did not give any output except for an empty array. Query tried to achieve the same

appointment.aggregate([
  { $match: { userId: user._id } },
  { $sort: { "dates.forDateAndShift.date": 1 } },
  {
    $lookup: {
      from: "doctors",
      localField: "doctorId",
      foreignField: "_id",
      let: { shiftId: "$dates.forDateAndShift.shift" },
      pipeline: [
        { $match: { "timeSlots.shifts": { $elemMatch: { _id: "$$shiftId" } } } }
      ],
      as: "doctor"
    }
  },
  { $unwind: "$doctor" },
  {
    $project: {
      status: 1,
      forUser: 1,
      dates: 1,
      serialNumber: 1,
      "doctor.name": 1,
      appointmentType: 1,
      "doctor.profile": 1
    }
  },
  { $group: { _id: "$status", appointments: { $push: "$$ROOT" } } },
  { $project: { _id: 0, status: "$_id", appointments: "$appointments" } }
])

I am writing this part of this question in order to avoid the warning of all code from stack overflow thus request you to kindly ignore this section

CodePudding user response：

One way to get the right shift in the doctor is:

db.appointment.aggregate([
  {
    $match: {userId: "6223370153c8126cd85884ce"}
  },
  {
    $sort: {"dates.forDateAndShift.date": 1}
  },
  {
    $lookup: {
      from: "doctors",
      let: {
        shiftId: "$dates.forDateAndShift.shift",
        doctorId: "$doctorId"
      },
      pipeline: [
        {
          $match: {$expr: {$eq: ["$_id", "$$doctorId"]}}
        },
        {
          $unwind: "$timeSlots"
        },
        {
          $project: {
            _id: 0,
            shifts: {
              $filter: {
                input: "$timeSlots.shifts",
                as: "item",
                cond: {$eq: ["$$item._id", "$$shiftId"]}
              }
            }
          }
        },
        {
          "$addFields": {"shiftsCount": {$size: "$shifts"}}
        },
        {
          $match: {shiftsCount: {$gt: 0}}
        },
        {
          $unset: "shiftsCount"
        }
      ],
      as: "doctor"
    }
  },
 // TODO: continue your query
])

You can see how it work on this playground example.

If the shift_id is unique, you can continue the $lookup pipeline with:

{$project: {shifts: {$arrayElemAt: ["$shifts", 0]}}}