I have a function that should return a count of users created in a given period and group by invited and non invited. On the $cond operator, I need to compare if the field tkbSponsor is not null and is not equals example@example.com. If this condition results to true, then the user was invited. Otherwise, he wasn't invited.
var countByPeriod = function(req,res) {
var initialDate = req.body.initialDate;
var finalDate = req.body.finalDate;
User.aggregate([
{
"$match": {
"timeStamp": {
"$gte": new Date(initialDate),
"$lt": new Date(finalDate)
}
}
},
{
"$group": {
"_id": null,
"total": { "$sum": 1 },
"invited": {
"$sum": {
"$cond": [
{
"tkbSponsor": {
"$nin": ["example@example.com",null]
}
},
1,
0
]
}
}
}
}
], (err,result) => {
if (!err) {
if (result.length) res.send(result[0]);
else res.send({"total": 0,"invited":0});
} else {
res.sendStatus(500);
console.log(err);
}
});
};
By the way, this function is giving me an error when executed:
{ [MongoError: invalid operator '$nin']
name: 'MongoError',
message: 'invalid operator \'$nin\'',
ok: 0,
errmsg: 'invalid operator \'$nin\'',
code: 15999 }
Just an observation. I used to use the $cond operator as below, because I didn't needed to compare with null:
"$cond": [
{
"$ne": ["$tkbSponsor", "example@example.com"]
},
1,
0
]
And it works. However, now I have also to compare if the tkbSponsor is not null and using $nin, is giving me that error.
Change that to use $and together with the $ifNull coalesce as :
{
"$cond": [
{
"$and": [
{ "$ne": ["$tkbSponsor", "example@example.com"] },
{
"$ne": [
{ "$ifNull": [ "$tkbSponsor", null ] },
null
]
}
]
}, 1, 0
]
}
or using $or as
{
"$cond": [
{
"$or": [
{ "$eq": ["$tkbSponsor", "example@example.com"] },
{
"$eq": [
{ "$ifNull": [ "$tkbSponsor", null ] },
null
]
}
]
}, 0, 1
]
}
The $ifNull operator's presence is to act as an $exists operator by replacing "non-existant" or null fields with a null value for evaluation.
Running this should return the correct results as the earlier revision was only evaluating documents where the tkbSponsor field exists AND has either a value of null or "example@example.com".
With $ifNull, "non-existant" fields are also evaluated as the operator gives them the null value.