Mongodb : Query based on time in ISODate format

问题:

Suppose the sample documents in Mongodb database are as follows:

 { "date" : ISODate("2015-11-09T05:58:19.474Z") }
 { "date" : ISODate("2014-10-25T07:30:00.241Z") }
 { "date" : ISODate("2015-11-30T15:24:00.251Z") }
 { "date" : ISODate("2012-01-10T18:36:00.101Z") }

Expected:

 { "date" : ISODate("2015-11-09T05:58:19.474Z") }
 { "date" : ISODate("2014-10-25T07:30:00.241Z") }

Iam interested in finding the documents whose time in "date" field is between 04:00 and 08:00 irrespective of day,month and year. Indirectly query has to match any "YYYY-MM-DDT" in date field.

My approach would be, query all the documents within presumed duration of dates from node and then for each document that matched the query, compare the "date" field of document with "yyyy-MM-DDT"+"required_time" ("YYYY-MM-DD is copied from each document's "date field" to compare by converting into moment() and get month,date and year") using moment.js module.

Is there any way to query to directly get the same results?

Note: I am using nodejs to connect to mongodb

回答1:

One approach is to use the aggregation framework, in particular the $redact operator which strips the document stream of content based on values within the document and its sub-documents. Depending on the result of a boolean expression, a document can be pruned from the stream, be included in the stream after its sub-documents have also been checked, or just passed complete into the stream. The idea behind $redact is to make the removal of sensitive information from the stream easy.

In your case, the criteria expression uses the $cond operator and the $and boolean operator to express the logical AND between the time ranges with the comparison operators $gt and $lt. Use the $hour date operator to return the hour for the date field as a number between 0 and 23. Thus your final aggregation looks like this:

db.collection.aggregate([
    {
        "$redact": {
            "$cond": {
                "if": { 
                    "$and": [
                        { "$gt": [ {"$hour": "$date"}, 4] },
                        { "$lt": [ {"$hour": "$date"}, 8] }
                    ]                 
                },
                "then": "$$KEEP",
                "else": "$$PRUNE"
            }
        }        
    }
])

Sample Output:

/* 0 */
{
    "result" : [ 
        {
            "_id" : ObjectId("56404450472fe25cc6b85886"),
            "date" : ISODate("2015-11-09T05:58:19.474Z")
        }, 
        {
            "_id" : ObjectId("56404450472fe25cc6b85887"),
            "date" : ISODate("2014-10-25T07:30:00.241Z")
        }
    ],
    "ok" : 1
}