Query in Array with dates

Question

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

Community · Accepted Answer · 2017-05-23 12:33:24Z

5

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
}

edited May 23, 2017 at 12:33

CommunityBot

11 silver badge

answered Jul 25, 2015 at 5:13

bagrat

7,5166 gold badges33 silver badges49 bronze badges

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

4 Comments

Blakes Seven Over a year ago

On the right track but basically the same as the OP's query. I think their gripe here is that the array in the document returned still has all the elements. So is there something you are not telling them here?

bagrat Over a year ago

The OP wants a query "...that will return a only hotel that have the element("available") equals 1...". In that case the original query is just fine, but has an error in the field name. However if the requirement is that only the elements of the availability array are returned that have available as 0, then Vishwas' answer will perfectly work, if he adds a $group stage to the pipeline.

Blakes Seven Over a year ago

Or just show them how to use the positional $ operator to return the "one" match within the document array.

bagrat Over a year ago

Updated, but if that's the case, the question needs to be redefined, agree? Thanks anyway.

Bernardo Maia · Accepted Answer · 2015-07-25 16:57:20Z

4

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

answered Jul 25, 2015 at 16:57

Bernardo Maia

1091 silver badge6 bronze badges

Comments

Vishwas · Accepted Answer · 2015-07-25 05:58:35Z

2

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

edited Jul 25, 2015 at 5:58

answered Jul 25, 2015 at 5:26

Vishwas

7,0777 gold badges46 silver badges70 bronze badges

3 Comments

bagrat Over a year ago

Aggregation is redundant here, and except the $unwind stage, the rest is the same as the OP's original query.

Vishwas Over a year ago

@n9code Aggregation is used to get only those documents where availability.available is 1. find will return all array containing availability.available = 0 as well as 1. In short it will return whole array if any match find.

bagrat Over a year ago

I agree, but the the problem OP has is in his query itself, and he does not state that he/she needs only the element of the array that has available as 1. Besides if there are multiple elements with available equals 1, the result will be multiple document. So you will need to add a $group stage as well.

Collectives™ on Stack Overflow

Query in Array with dates

3 Answers 3

4 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related