CODEWITHSUNDEEP
mongodbmongodb-query
TBE
TBE

Reputation: 1133

Query mongoDB for a date between two dates misses some documents

I have the following document in a collection name chats:

{
    "_id" : 25281,
    "conversation" : [ 
        {
            "time" : "1970-01-18T20:16:28.988Z"
        }, 
        {
            "time" : "2018-11-09T18:43:09.297Z"
        }
    ],
}

For some reason this specific document is not returning in the below query, although similar documents are returning as expected.

Here is the query:

db.getCollection('chats').find({"conversation.1.time": 
    { 
        "$gte": ISODate("2018-11-09T00:00:00.000Z"), 
        "$lt": ISODate("2018-11-10T00:00:00.000Z")
    }  
})

Upvotes: 1

Views: 68

Answers (1)

mickl
mickl

Reputation: 49995

This document is not matched because there's a mismatch between ISODate specified in your query and string in your data model. MongoDB checks types before values so that's why you're getting no values. From docs

For most data types, however, comparison operators only perform comparisons on documents where the BSON type of the target field matches the type of the query operand.

There are three ways to fix that. You can change the type in your query:

db.getCollection('chats').find({"conversation.1.time": 
    { 
        "$gte": "2018-11-09T00:00:00.000Z", 
        "$lt": "2018-11-10T00:00:00.000Z"
    }  
})

or you need to convert the data in chats:

{
    "_id" : 25281,
    "conversation" : [ 
        {
            "time" : ISODate("1970-01-18T20:16:28.988Z")
        }, 
        {
            "time" : ISODate("2018-11-09T18:43:09.297Z")
        }
    ],
}

Alternatively you can take a look at $toDate operator in Aggregation Framework (introduced in MongoDB 4.0):

db.getCollection('chats').aggregate([
    {
        $addFields: {
            value: { $arrayElemAt: [ "$conversation", 1 ] }
        }
    },
    {
        $match: {
            $expr: {
                $and: [
                    { $gte: [ { $toDate: "$value.time" }, ISODate("2018-11-09T00:00:00.000Z") ] },
                    { $lt: [ { $toDate: "$value.time" }, ISODate("2018-11-10T00:00:00.000Z") ] },
                ]
            }
        }
    }
])

Upvotes: 1

Related Questions