Menu
I am trying to write an aggregation that counts how many documents have certain fields (ie only count them if they are present). The objects look something like this:
{
"_id" : ObjectId("5617731fe65e0b19101c7039"),
"dateCreated" : ISODate("2015-10-09T07:56:15.068Z"),
"dateSent" : ISODate("2015-10-09T07:56:16.682Z"),
"dateAttempted" : ISODate("2015-10-09T07:56:16.682Z")
},
{
"_id" : ObjectId("561e37bb537d381bb0ef0ae2"),
"dateCreated" : ISODate("2015-10-14T11:08:43.306Z"),
"dateSent" : ISODate("2015-10-14T11:09:51.618Z"),
"dateAttempted" : ISODate("2015-10-14T11:09:51.618Z"),
"dateViewed" : ISODate("2015-10-15T10:09:50.618Z"),
"dateOpened" : ISODate("2015-10-15T10:10:01.618Z")
}
I want to iterate over all documents, counting where the field exists. Desired output:
{
"total" : 1000,
"created" : 1000,
"sent" : 990,
"attempted" : 995
"viewed" : 800,
"opened" : 750
}
Bonus points if this output can be grouped per day! I would prefer not to perform a new aggregation for each date in the range.
Here's what I have so far, which doesn't work; it returns zeros for each field
[
{
"$group": {
"_id": {
"$dayOfMonth": "$dateCreated"
},
"total": {
"$sum": 1
},
"sent": {
"$sum": "$dateSent"
},
"attempted": {
"$sum": "$dateAttempted"
},
"viewed": {
"$sum": "$dateViewed"
},
"clicked": {
"$sum": "$dateClicked"
}
}
}
]
705
asked Oct 15 '15 08:10
MongoDB $count AggregationThe MongoDB $count operator allows us to pass a document to the next phase of the aggregation pipeline that contains a count of the documents. There a couple of important things to note about this syntax: First, we invoke the $count operator and then specify the string.
To count the unique values, use "distinct()" rather than "find()", and "length" rather than "count()". The first argument for "distinct" is the field for which to aggregate distinct values, the second is the conditional statement that specifies which rows to select.
var value = db. collection. count(); and then print(value) or simply value , would give you the count of documents in the collection named collection .
The $cond and $ifNull operators are the helpers here:
[
{
"$group": {
"_id": {
"$dayOfMonth": "$dateCreated"
},
"total": {
"$sum": 1
},
"sent": {
"$sum": { "$cond": [ { "$ifNull": [ "$dateSent", false ] }, 1, 0 ] }
},
"attempted": {
"$sum": { "$cond": [ { "$ifNull": [ "$dateAttempted", false ] }, 1, 0 ] }
},
"viewed": {
"$sum": { "$cond": [ { "$ifNull": [ "$dateViewed", false ] }, 1, 0 ] }
},
"clicked": {
"$sum": { "$cond": [ { "$ifNull": [ "$dateClicked", false ] }, 1, 0 ] }
}
}
}
]
$ifNull will return either the field where present ( a logical true ) or the alternate value false. And the $cond looks at this condition and returns either 1 where true or 0 where false to provide the conditional count.
92
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
