CODEWITHSUNDEEP
mongodbmongodb-query
Bernardo Maia
Bernardo Maia

Reputation: 109

Query in Array with dates

I'm trying to do a query(In MongoDB) in array("availability") that will return a only hotel that have the element("available") equals 1 and between the dates inside the availability.

But the query return all hotels when the correct return is "Mercato Hotel"

Query that i have used without success:

{city: "Boston", availability: { $elemMatch: {availability: 1, date: {$gte: ISODate("2015-05-02T00:00:00.000+0000")}, date: {$lte: ISODate("2015-05-04T00:00:00.000+0000")}}}}

Json in MongoDb:

 { 
        "_id" : ObjectId("55b302ee8debdf1a908cdc85"), 
        "city" : "Boston", 
        "hotel" : "Mercatto Hotel", 
        "availability" : [
            {
                "date" : ISODate("2015-05-01T00:00:00.000+0000"), 
                "available" : NumberInt(0)
            }, 
            {
                "date" : ISODate("2015-05-02T00:00:00.000+0000"), 
                "available" : NumberInt(0)
            }, 
            {
                "date" : ISODate("2015-05-03T00:00:00.000+0000"), 
                "available" : NumberInt(0)
            }, 
            {
                "date" : ISODate("2015-05-04T00:00:00.000+0000"), 
                "available" : NumberInt(1)
            }
        ]
    }
    { 
        "_id" : ObjectId("55b302ee8debdf1a908cdc89"), 
        "city" : "Boston", 
        "hotel" : "Hostel Villa", 
        "availability" : [
            {
                "date" : ISODate("2015-05-01T00:00:00.000+0000"), 
                "available" : NumberInt(1)
            }, 
            {
                "date" : ISODate("2015-05-02T00:00:00.000+0000"), 
                "available" : NumberInt(0)
            }, 
            {
                "date" : ISODate("2015-05-03T00:00:00.000+0000"), 
                "available" : NumberInt(0)
            }, 
            {
                "date: ISODate("2015-05-04T00:00:00.000+0000"), 
                "available" : NumberInt(0)
            }
        ]
    }

Someone can help me?

Thanks...

Upvotes: 7

Views: 8226

Answers (3)

Bernardo Maia
Bernardo Maia

Reputation: 109

The query:

{
    city: "Boston", 
    availability: {
            $elemMatch: {
                    available: 1, 
                    date: {
                        $gte: ISODate("2015-05-02T00:00:00.000+0000"), 
                        $lte: ISODate("2015-05-04T00:00:00.000+0000")
                    }
            }
    }
}

Returned the same result. In other words, returned all hotels when the correct return is "Mercato Hotel".

Upvotes: 4

Vishwas
Vishwas

Reputation: 7067

You can use aggregation to get expected output as following:

db.collection.aggregate({
    $unwind: "$availability"
}, {
    $match: {
    "city": "Boston",
    "availability.available": 1,
    "availability.date": {
        $gte: ISODate("2015-05-02T00:00:00.000+0000"),
        $lte: ISODate("2015-05-04T00:00:00.000+0000")
    }
    }
})

Edit

If there are multiple available=1 then use following query:

db.collection.aggregate({
    $unwind: "$availability"
}, {
    $match: {
    "city": "Boston",
    "availability.available": 1,
    "availability.date": {
        $gte: ISODate("2015-05-02T00:00:00.000+0000"),
        $lte: ISODate("2015-05-04T00:00:00.000+0000")
    }
    }
}, {
    $group: {
    _id: "$hotel",
    "city": {
        $first: "$city"
    },
    "availability": {
        $push: "$availability"
    }
    }
})

Upvotes: 2

bagrat
bagrat

Reputation: 7468

You have got a typo in your query, availability instead of available, should be this:

{
    city: "Boston", 
    availability: {
            $elemMatch: {
                    available: 1, 
                    date: {
                        $gte: ISODate("2015-05-02T00:00:00.000+0000"), 
                        $lte: ISODate("2015-05-04T00:00:00.000+0000")
                    }
            }
    }
}

UPDATE with Blakes Seven

If you want to get only the element of availability array that matches your query, add projection:

{
    "city": 1,
    "hotel": 1
    "availability.$.date": 1
}

Upvotes: 5

Related Questions