aggregation framework sum existence of fields whose value is a date

[Nefiga]

I have a schema like this:

{
        "_id" : ObjectId("51844cc3429c79d53e000001"),
        "category" : 1
        "events" : {
                "early" : ISODate("2013-05-04T00:52:27.098Z"),
                "late" : ISODate("2013-05-04T00:52:27.564Z")
        }
},
{
        "_id" : ObjectId("51844cc3429c79d53e000001"),
        "category" : 2
        "events" : {
                "early" : ISODate("2013-05-04T00:52:27.098Z"),
        }
},
{
        "_id" : ObjectId("51844cc3429c79d53e000001"),
        "category" : 1
        "events" : {
                "late" : ISODate("2013-05-04T00:52:27.564Z")
        }
}

What I want output is

"result" : [
                {
                        "_id" : {
                                "category" : "1"
                        },
                        "total" : 2,
                        "early" : 1,
                        "late" : 2
                },

                {
                        "_id" : {
                                "category" : "2"
                        },
                        "total" : 1,
                        "early" : 1
                }
        ],


I have tried different ways of getting the events.early and events.late to transform into an int so I can sum it, but I can't find the solution.

Example:

db.data.aggregate(
{ $project : {
oid : 1,
early : {$cond : [{"events.early" : {$exists : true}}, 1, 0]}
late : {$cond : [{"events.late" : {$exists : true}}, 1, 0]}
} }
);

But I can't use dot notation in the field for $project. I can't use ifNull because i need the value to transform so I can $sum it in a $group later. I can't $count it because then I have to match it, and then I lose the ability to count the total.

Any help?

[Nefiga]

I have an ugly solution based off a reply by someone else who answered a similar question for me many months ago.

db.data.aggregate(
{ $project : {
oid : 1,
early : {$cond : [{$eq : [{$ifNull : ["$events.early", ""]}, ""]}, 0, 1]},
late : {$cond : [{$eq : [{$ifNull : ["$events.late", ""]}, ""]}, 0, 1]}
} }
);

Kinda ugly, is there a different way to do this?

[Asya Kamsky]

You are very close to a prettier solution  :)

I do want to point out to OP that you had some syntax errors in your aggregation (missing $ in front of "events.early", some missing commas) so you may have been closer than you thought, but you're also forgetting to project the category field so you wouldn't be able to group on a field you don't project.

Here is the projection you want with slightly prettier syntax (more compact).  Rather than using "" (empty string) I chose to use a date way in the past (I declared it as a variable) but it really doesn't matter what you use, of course, as long as you compare it to itself:

var never = new Date(0);

db.data.aggregate(
   { $project : {
          category : 1,
          early : {$cmp : [ {$ifNull : ["$events.early", never ] }, never ] },
          late   : {$cmp : [ {$ifNull : ["$events.late",    never ] }, never ] }
   } }
);

I'm taking advantage of the fact that the $cmp comparison operator returns an integer - 0 when two strings are equal and 1 when the first value is greater than the second one (which is why I made my "never" a date type).

Asya