MongoDB Get Single Object Nested in Array of Objects in Array of Objects

Question

How do you access a single object nested in an array of objects that is nested in another array of objects, based on a few property values, something like this in pseudo code:

SELECT DAY=1 WHERE _id=5a3469f22dc3784bdd9a6190 AND MONTH=12

Mongoose model schema is listed below. As required, subdocuments are listed higher than their corresponding parents, dailySchedulesSchema being the highest:

var dailySchedulesSchema = new mongoose.Schema({
    day: Number,
    dayStart: Number,
    firstBreakStart: Number,
    firstBreakEnd: Number,
    lunchStart: Number,
    lunchEnd: Number,
    secondBreakStart: Number,
    secondBreakEnd: Number,
    dayEnd: Number,
    workDuration: Number
});

var monthlyScheduleSchema = new mongoose.Schema({
    month: {type: Number, required: true },
    dailySchedules: [dailySchedulesSchema]
});

var employeeSchema = new mongoose.Schema({
    name: {type: String, required: true},
    surname: {type: String, required: true},
    email: {type: String, required: true},
    phone: {type: String, required: true},
    occupation: {type: String, required: true},
    status: {type: Boolean, required: true},
    monthlySchedule: [monthlyScheduleSchema]
});

This is the employee entry in the database I am trying to work on.:

"_id" : ObjectId("5a3469f22dc3784bdd9a6190"),
        "name" : "Eric",
        "surname" : "K. Farrell",
        "email" : "[email protected]",
        "phone" : "864-506-7281",
        "occupation" : "Employee",
        "status" : true,
        "monthlySchedule" : [
                {
                        "month" : 12,
                        "dailySchedules" : [
                                {
                                        "day" : 1,
                                        "dayStart" : 480,
                                        "firstBreakStart" : 600,
                                        "firstBreakEnd" : 615,
                                        "lunchStart" : 720,
                                        "lunchEnd" : 750,
                                        "secondBreakStart" : 870,
                                        "secondBreakEnd" : 885,
                                        "dayEnd" : 1020,
                                        "workDuration" : 480
                                },
                                {
                                        "day" : 2,
                                        "dayStart" : 540,
                                        "firstBreakStart" : 630,
                                        "firstBreakEnd" : 645,
                                        "lunchStart" : 750,
                                        "lunchEnd" : 780,
                                        "secondBreakStart" : 870,
                                        "secondBreakEnd" : 885,
                                        "dayEnd" : 1050,
                                        "workDuration" : 480
                                }
                        ]
                }
        ]
}

The route itself for getting single day is: "/employees/:employeeid/:month/:day" .

While I managed to access the parent documents(like listing all employees), I was unable to list specific subdocument entries(like specific day schedule for that employee) - mongoose has either been returning all of the existing day schedules in the month or nothing at all:

(...)

  var sendJsonResponse = function(res, status, content){
        res.status(status);
        res.json(content);
    }

(...)

module.exports.empDayReadOne = function(req, res){
    var monthParam = req.params.month;
    var employeeid = req.params.employeeid;
    var dayParam = req.params.day;

    Emp
        .aggregate([
        {$match: {$and: [{'monthlySchedule': {$elemMatch: {$exists: true} } }, {_id: employeeid }] } },
        {$unwind:'$monthlySchedule'}, 
        {$unwind:'$monthlySchedule.dailySchedules'}, 
        {$match:{ $and:[ {'monthlySchedule.dailySchedules.day': dayParam},{'monthlySchedule.month': monthParam} ] } }

        ])
        .exec(function(err, dailySchedule){
            if(dailySchedule){
                sendJsonResponse(res, 200, dailySchedule);
            } else if(err){
                sendJsonResponse(res, 400, err);
                return;
            } else {
                sendJsonResponse(res, 404, {"message": "This day has no schedules added."});
                return;
            }

        });

};

Answer 1

You can try below aggregation query.

The below query first $filters monthlySchedule which returns the array with the matching month array element followed by second filter to retrieve the matching day element in dailySchedules array.

$arrayElemAt to convert the array with single element into a document.

db.collection_name.aggregate([
  {"$match":{"_id":ObjectId("5a3469f22dc3784bdd9a6190")}},
  {"$project":{
    "dailyschedule":{
      "$arrayElemAt":[
        {"$filter":{
          "input":{
            "$filter":{
              "input":"$monthlySchedule",
              "cond":{"$eq":["$$this.month",12]}
            }
          },
          "cond":{"$eq":["$$this.day",1]
          }
        }},
      0]
    }
  }}
])

Updated Mongoose code:

Emp.aggregate([
  {"$match":{"_id":mongoose.Types.ObjectId(employeeid)}},
  {"$project":{
    "dailyschedule":{
      "$arrayElemAt":[
        {"$filter":{
          "input":{
            "$filter":{
              "input":"$monthlySchedule",
              "cond":{"$eq":["$$this.month",monthParam]}
            }
          },
          "cond":{"$eq":["$$this.day",dayParam]
          }
        }},
      0]
    }
  }}
])