I am doing an aggregation in Mongo and I cannot get the expected results; I want to count those with a null column and those with not null but apparently { "$ne" : ["$RequestedOn", null] } is coming always as true.
I cannot really see where the problem is. This is the data in the collection
/* 1 */
{
"_id" : ObjectId("56cf03445667a09b17f661ef"),
"Name" : "User1 Test1",
"AccountRef" : "AccountRef1",
"Voucher" : "Voucher1",
"Email" : "AccountRef1@server.ie",
"CampaignId" : ObjectId("56c752d439bac5655eec7fb7")
}
/* 2 */
{
"_id" : ObjectId("56cf034c5667a09b17f661f0"),
"Name" : "User2 Test2",
"AccountRef" : "AccountRef2",
"Voucher" : "Voucher2",
"Email" : "AccountRef1@server.ie",
"CampaignId" : ObjectId("56c752d439bac5655eec7fb7")
}
/* 3 */
{
"_id" : ObjectId("56cf03565667a09b17f661f1"),
"Name" : "User3 Test3",
"AccountRef" : "AccountRef3",
"Voucher" : "Voucher3",
"GuidLink" : "7f079244-d94f-5e4a-8096-59b6df3ef64f",
"Email" : "AccountRef1@server.ie",
"CampaignId" : ObjectId("56c752d439bac5655eec7fb7")
}
/* 4 */
{
"_id" : ObjectId("56cf035e5667a09b17f661f2"),
"Name" : "User4 Test4",
"AccountRef" : "AccountRef4",
"Voucher" : "Voucher4",
"Email" : "AccountRef1@server.ie",
"RequestedOn" : ISODate("2016-02-29T22:49:20.201Z"),
"CampaignId" : ObjectId("56c752d439bac5655eec7fb7")
}
And this is how I build the query
var wlUsers=Database.Collection;
var cs = wlUsers.AsQueryable()
.Where(wl => wl.CampaignId == id)
.Select(p=> new { id = p.CampaignId, p.RequestedOn})
.GroupBy(wl => wl.id)
.Select(g => new CampaignStatistics{
ExistingCustomers = g.Count(),
TotalOrdered = g.Sum(w => w.RequestedOn.HasValue ? 1 :0),
LastOrder = g.Max(w => w.RequestedOn),
FirstOrder = g.Min(w => w.RequestedOn),
Last24hOrders = g.Sum(w =>
w.RequestedOn > DateTime.Today.AddDays(-1)?1:0),
});
The result is
{
"ExistingCustomers" : 4,
"TotalOrdered" : 4,
"LastOrder" : ISODate("2016-02-29T22:49:20.201Z"),
"FirstOrder" : ISODate("2016-02-29T22:49:20.201Z"),
"Last24hOrders" : 4
}
But it should be
{
"ExistingCustomers" : 4,
"TotalOrdered" : 1,
"LastOrder" : ISODate("2016-02-29T22:49:20.201Z"),
"FirstOrder" : ISODate("2016-02-29T22:49:20.201Z"),
"Last24hOrders" : 0
}
So the totalOrdered has to be 1 and the 'Last24hOrders' = 0.
Also I am new on Mongo so I'll appreciate any tips advises on how to build these projections