mongodb query nested array with date field

Question

this is my document .

"calendar": {
        "_id": "5cd26a886458720f7a66a3b8",
        "hotel": "5cd02fe495be1a4f48150447",
        "calendar": [
            {
                "_id": "5cd26a886458720f7a66a413",
                "date": "1970-01-01T00:00:00.001Z",
                "rooms": [
                    {
                        "_id": "5cd26a886458720f7a66a415",
                        "room": "5cd17d82ca56fe43e24ae5d3",
                        "price": 10,
                        "remaining": 8,
                        "reserved": 0
                    },
                    {
                        "_id": "5cd26a886458720f7a66a414",
                        "room": "5cd17db6ca56fe43e24ae5d4",
                        "price": 12,
                        "remaining": 8,
                        "reserved": 0
                    },
                 {
                        "_id": "5cd26a886458720f7a66a34",
                        "room": "5cd17db6ca45fe43e24ae5e7",
                        "price": 0,
                        "remaining": 0,
                        "reserved": 0
                    }
                ]
            },
   }

and this is my shema:

const calendarSchema = mongoose.Schema({
    hotel: {
        type: mongoose.Schema.ObjectId,
        ref: "Hotel",
        required: true
    },
    city: {
        type: mongoose.Schema.ObjectId,
        ref: "City"
    },
    calendar: [
        {
            date: Date,
            rooms: [
                {
                    room: {
                        type: mongoose.Schema.ObjectId,
                        ref: "Room",
                        required: true
                    },
                    price: {
                        type: Number
                    },
                    remaining: {
                        type: Number
                    },
                    reserved: {
                        type: Number
                    }
                }
            ]
        }
    ]
});

First of all, as you can see my calendar stores hotelId and CityId and included another calendar that contains some objects. There is nothing fancy here. The query has two conditions as below:

1.Our specific filter is located whole dates between startDate and endDate

2.Mentioned filter only shows the room's prices and remaining ( Not included zero num ).

And after injecting this conditions, query must return only the rooms that are matched with my filter. I tried some query but the outcome is not my result .

db.calendars.find({ 
  'calendar': { 
      '$elemMatch': { 
           date: { 
             '$lt': ISODate("2019-05-09T09:37:24.005Z"), 
             '$lt': ISODate("2019-06-05T09:37:24.005Z")
           },
           "rooms.$.price": { '$gt': 0 },
           "rooms.$.remaining": { '$gt': 0 }
      }
   }
})  

Answer 1

Unfortunately this is not THAT easy as you describe, this cannot be done with just a find assuming you want to project ONLY (and all) the rooms that match.

However with an aggregate this is possible, it would look like this:

db.calendars.aggregate([
    {
        $project:
            {
                "rooms": {
                    $filter: {
                        input: {
                            "$map": {
                                "input": "$calendar",
                                "as": "cal",
                                "in": {
                                    "$cond": [
                                        {
                                            $and: [{$gt: ["$$cal.date", ISODate("2019-05-09T09:37:24.005Z")]},
                                                {$lt: ["$$cal.date", ISODate("2019-06-05T09:37:24.005Z")]},]
                                        },
                                        {
                                            "rooms": {
                                                "$filter": {
                                                    "input": "$$cal.rooms",
                                                    "as": "room",
                                                    "cond": {
                                                        $and: [{"$gt": ["$$room.price", 0]},
                                                            {"$gt": ["$$room.remaining", 0]}]
                                                    }
                                                }
                                            },
                                            date: "$$cal.date"
                                        },
                                        null
                                    ]
                                }
                            },
                        },
                        as: 'final',
                        cond: {$size: {$ifNull: ["$$final.rooms", []]}}
                    }
                },

            }
    },
    {
        $match: {
            "rooms.0": {$exists: true}
        }
    }
])