MongoDb aggregate Match date part

Question

I'm trying to do this query, basically get all the timepoints which are 9am-9.59am:

db.DataPoints.find({$where: "new Date(this.Head.Timestamp).getHours() === 9"}, {_id: 0, 'Body.PAC.Values.1': 1})

but as an aggregate query. You can't do a where in match

db.DataPoints.aggregate([{ $match : { "Head.Timestamp": { $hour: 9 } } },...

Doesn't work. Searching so far has yielded nothing.

edit: A sample document is as follows:

{
"_id" : "d5c75619-fe17-4618-9333-004e3ee67cb3",
"Head" : {
    "Timestamp" : ISODate("2015-05-20T04:20:00.000Z"),
    "RequestArguments" : {
        "Query" : "Inverter",
        "Scope" : "System"
    },
    "Status" : {
        "Code" : 0,
        "Reason" : "",
        "UserMessage" : ""
    }
},
"Body" : {
    "PAC" : {
        "Unit" : "W",
        "Values" : {
            "1" : 55
        }
    },
    "DAY_ENERGY" : {
        "Unit" : "Wh",
        "Values" : {
            "1" : 24
        }
    },
    "YEAR_ENERGY" : {
        "Unit" : "Wh",
        "Values" : {
            "1" : 55017
        }
    },
    "TOTAL_ENERGY" : {
        "Unit" : "Wh",
        "Values" : {
            "1" : 119228
        }
    }
}
}

chridam · Accepted Answer · 2017-01-26T09:36:05.073

A much better approach would be to use the $redact pipeline which is bit nicer than using $project for a logical condition and then using $match to filter that logical match. It will return all documents match the condition using $$KEEP and discards those that don't match using the $$PRUNE variable:

db.DataPoints.aggregate([
    { 
        "$redact": {
            "$cond": {
                "if": { 
                    "$eq": [
                        { "$hour" : "$Head.Timestamp" },
                        9
                    ]
                },
                "then": "$$KEEP",
                "else": "$$PRUNE"
            }
        }   
    }
])

The longer approach would be to create a new field with the $project operator that stores the hour value which you can then use in the next $match pipeline:

db.DataPoints.aggregate([
    { 
        "$project" : { 
             "hour" : { "$hour" : "$Head.Timestamp" },
             "Head": 1,
             "Body": 1 
        } 
    },
    { "$match": { "hour": 9 } }
])

That's great, would it be more performant as a mapReduce query? — johnstaveley, Jun 22 '15 at 10:04
On the contrary, aggregation would be faster than mapReduce since it runs natively in the server (C++), and mapReduce spawns separate javascript thread(s) to run JS code. You could refer to this [**answer**](http://stackoverflow.com/a/13912126/) for an in-depth analysis. — chridam, Jun 22 '15 at 12:04

MongoDb aggregate Match date part

1 Answers1