Mongo: Using $dayOfWeek in a $match clause when query has also a $groupy

Question

I have a mongo document in which some registers are stored by with a date, for instance in a very simplified manner:

{
    "vehicleId" : "vehicle4",
    "telemetryDate" : ISODate("2013-06-22T05:00:00Z"),
    "alarmsTotal" : 9
}
{
    "vehicleId" : "vehicle5",
    "telemetryDate" : ISODate("2013-06-20T05:00:00Z"),
    "alarmsTotal" : 2
}

I need to perform a group by the vhicleId, summing the alarm total. Nevertheless there is an option to exclude or not the Weekends (Saturday, Sunday).

I have been searching to no avail the answer to this question with Mongo. My query, without taking the exclude weekends into account that works correctly is:

{
   "aggregate":"telemetrySummary",
   "pipeline":[
      {
         "$match":{
            "telemetryDate":{
               "$gte":{
                  "$date":"2013-06-20T05:00:00.000Z"
               },
               "$lte":{
                  "$date":"2013-06-24T05:00:00.000Z"
               }
            }
         }
      },
      {
         "$group":{
            "_id":{
               "vehicleId":"$vehicleId"
            },
            "alarms":{
               "$sum":"$alarmsTotal"
            }
         }
      }
   ]
}

I need to exclude the values where the mongo operator $dayOfWeek is 1 (sunday) and 7 (sundays). I tried through many queries, one of the logical ones being:

{
   "aggregate":"telemetrySummary",
   "pipeline":[
      {
         "$match":{
            "telemetryDate":{
               "$gte":{
                  "$date":"2013-06-20T05:00:00.000Z"
               },
               "$lte":{
                  "$date":"2013-06-24T05:00:00.000Z"
               }
            },
            "{ \"$dayOfWeek\" : \"$telemetryDate\"}":{
               "$in":[2,3,4,5,6]
            }
         }
      },
      {
         "$group":{
            "_id":{
               "vehicleId":"$vehicleId"
            },
            "alarms":{
               "$sum":"$alarmsTotal"
            }
         }
      }
   ]
}

I think my real problem is basically that I do not know how to store a $dayOfWeek operation over a non-grupable variable as a telemetryDate into a day variable that I can compare with a $in operator.

Thanks for reading and hoping to find some guidance :)

score 4 · Answer 1 · answered Jun 21 '13 at 17:51

4

You should use $dayOfWeek inside $project like so:

[{<your match by date>}, {$project:{vehicleId:1, telemetryDate:1, alarmsTotal:1, dow:{$dayOfWeek:'$telemetryDate'}}}, {$match:{dow:{$in:[2,3,4,5,6]}}}, <rest of pipeline> ]

btw, your first $match can benefit from using index, like normal find command. It will improve performance on large datasets.

answered Jun 21 '13 at 17:51

Artem Mezhenin

5,539
6
32
51

Thanks a lot for your reply, still I wanted to ask you: $project acts like a select, nevertheless the telemetryDate cannot be grouped, can it? Will Mongo be able to perform the comparison this way?. I will for sure check it out today and get back to you... thanks!!! – will824 Jun 21 '13 at 20:02
you are not grouping on date, you are grouping on vehicleId though. – Asya Kamsky Jun 22 '13 at 03:38
You can group by date, by vehicleId or by combination of vehicleId and date. Give me a comment, if have problems with grouping. – Artem Mezhenin Jun 22 '13 at 04:26

score 3 · Accepted Answer · answered Jun 22 '13 at 03:37

It's actually pretty simple to do this - you can use $project to create new fields that didn't exist in the original document - you can compute new values or you can conditionally project different values, depending on what original values are. You can even calculate in a single group phase both total alarms as well as only alarms on weekdays.

Here is what you need to do in the project phase:

{"$project" : {
        "vehicleId" : 1,
        "telemetryDate" : 1,
        "alarmsTotal" : 1,
        "alarmsWeekdays" : {
            "$cond" : [
                {
                    "$or" : [
                        {
                            "$eq" : [
                                {
                                    "$dayOfWeek" : "$telemetryDate"
                                },
                                1
                            ]
                        },
                        {
                            "$eq" : [
                                {
                                    "$dayOfWeek" : "$telemetryDate"
                                },
                                7
                            ]
                        }
                    ]
                },
                0,
                "$alarmsTotal"
            ]
        }
    }
}

The above says: "Pass through these three fields, but also compute a new field alarmWeekdays like so: ..."

The actual expression in more "English" terms happens to be:

IF `$dayOfWeek` of telemetryDate is equal to 1 
OR `$dayOfWeek` of telemetryDate is equal to 7
THEN `$project` the value 0 as `alarmsWeekdays
ELSE `$project` the value from `$alarmsTotal` as `alarmsWeekdays`

My final implementation used Asya's answer, although amezhenin answer would perfectly work as well. I tested the project variant and also implementing the match { alarmsWeekdays : 1 }. Having 1 match instead of 2 worked as well for my case. — will824, Jul 08 '14 at 19:05

Mongo: Using $dayOfWeek in a $match clause when query has also a $groupy

2 Answers2