CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
user3812127
user3812127

Reputation: 75

Aggregation Conditional Count on Present Fields

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"
      }
    }
  }
]

Upvotes: 6

Views: 5825

Answers (1)

Blakes Seven
Blakes Seven

Reputation: 50406

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.

Upvotes: 11

Related Questions