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-01 14:54:32Z

3

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

edited Jul 1, 2019 at 14:54

answered May 12, 2019 at 15:09

Tom Slabbaert

22.5k10 gold badges34 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Babak Abadkheir Over a year ago

tnx for your answer, but it complaining about $size : "The argument to $size must be an array, but was of type: missing"

omid Over a year ago

@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.

Tom Slabbaert Over a year ago

@omid i'm sorry i didn't quiet understand, you need to do the same query with minor differences?

omid Over a year ago

@tomslabbaert yes, see this question please, stackoverflow.com/questions/56850607/…

Collectives™ on Stack Overflow

mongodb query nested array with date field

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related