CODEWITHSUNDEEP
mongodbdateaggregation-framework
Pratik Bothra
Pratik Bothra

Reputation: 2694

Result mismatch between elem match, and greater than and less than in mongodb

I have a document like this below

{
    "_id" : ObjectId("56df07927a65825b47d5e77d"),
    "internal_id" : 67627,
    "created_at" : ISODate("2015-12-14T16:10:11.015Z"),
    "updated_at" : ISODate("2016-01-16T12:40:57.328Z"),
    "name" : "DPA_Top Brands",
    "company_id" : 2,
    "ad_account_id" : 15,
    "created_time" : ISODate("2015-12-14T15:51:47.000Z"),
    "daily" : [ 
        {
            "fb_impressions" : 1335,
            "fb_clicks" : 93,
            "fb_spend" : 1098.3499999999999091,
            "timestamp" : "2015-12-16"
        }, 
        {
            "fb_impressions" : 5072,
            "fb_clicks" : 475,
            "timestamp" : "2016-01-19"
        }, 
        {
            "fb_impressions" : 322,
            "fb_clicks" : 37,
            "timestamp" : "2016-01-16"
        }
    ]
}

This does not return the above document

db.getCollection('ads').find({'ad_account_id': 15, 'daily': {'$elemMatch': {'timestamp': {'$lte': '2016-01-15', '$gte': '2016-01-01'}}}})

But this returns the above document

db.getCollection('ads').find({'ad_account_id': 15, 'daily.timestamp': {'$lte': '2016-01-15', '$gte': '2016-01-01'}})

The above query is clearly incorrect, and should not return data as no data is between the two dates.

I believe its the timestamp with '2015' which is really messing up things, but I don't get why. Why is the result of the two different? The elem-match is clearly correct.

Attached output

MongoDB shell version: 3.2.1 connecting to: test

> db.getCollection('ads').find({'ad_account_id': 15, 'daily.timestamp': {'$lte': '2016-01-15', '$gte': '2016-01-01'}})
{ "_id" : ObjectId("56df07927a65825b47d5e77d"), "internal_id" : 67627, "created_at" : ISODate("2015-12-14T16:10:11.015Z"), "updated_at" : ISODate("2016-01-16T12:40:57.328Z"), "name" : "DPA_Top Brands", "company_id" : 2, "ad_account_id" : 15, "created_time" : ISODate("2015-12-14T15:51:47Z"), "daily" : [ { "fb_impressions" : 1335, "fb_clicks" : 93, "fb_spend" : 1098.35, "timestamp" : "2015-12-16" }, { "fb_impressions" : 5072, "fb_clicks" : 475, "timestamp" : "2016-01-19" }, { "fb_impressions" : 322, "fb_clicks" : 37, "timestamp" : "2016-01-16" } ] }
> db.getCollection('ads').find({'ad_account_id': 15, 'daily': {'$elemMatch': {'timestamp': {'$lte': '2016-01-15', '$gte': '2016-01-01'}}}})
>

Thanks in advance

Upvotes: 1

Views: 507

Answers (1)

Arindam Barman
Arindam Barman

Reputation: 107

There is nothing wrong with the date comparision.

You cannot use find with multiple query conditions to search a nested array of documents ideal way is to use $query. 'daily' is an array is daily.timestamp is not valid. However you can use 'daily.0.timestamp' to get the correct value. When you are using find with multiple conditions, if any of the conditions are met it will return you the document.

Upvotes: 1

Related Questions