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 }
      }
   }
})

Tom Slabbaert · Accepted Answer · 2019-07-01T14:54:32.900

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}
        }
    }
])

tnx for your answer, but it complaining about $size : "The argument to $size must be an array, but was of type: missing" — Babak Abadkheir, May 13 '19 at 05:54
@tom-slabbaert i have a similar document and requirement, your query very useful, but `price` and `remaining` conditions check first date rooms, in data from 01/02/2019 to 05/02/2019 and price on 03/02/2019 is 0, should return false, but return calendar, but if this happen in first date `01/02/2019`, conditions worked. — omid, Jul 01 '19 at 14:52
@omid i'm sorry i didn't quiet understand, you need to do the same query with minor differences? — Tom Slabbaert, Jul 02 '19 at 11:24
@tomslabbaert yes, see this question please, https://stackoverflow.com/questions/56850607/mongodb-advance-complex-multi-filter-query — omid, Jul 02 '19 at 13:21

mongodb query nested array with date field

1 Answers1