CODEWITHSUNDEEP
mongodb
okoboko
okoboko

Reputation: 4482

MongoDB: How to count a field if it's value matches a condition?

I have a document that includes a field like this:

{
 ...
 log: [
    {
      utc_timestamp: ISODate("2014-11-15T10:26:47.337Z"),
      type: "clicked"
    },
    {
      utc_timestamp: ISODate("2014-10-15T16:12:51.959Z"),
      type: "emailed"
    },
    {
      utc_timestamp: ISODate("2014-10-15T16:10:51.959Z"),
      type: "clicked"
    },
    {
      utc_timestamp: ISODate("2014-09-15T04:59:19.431Z"),
      type: "emailed"
    },
    {
      utc_timestamp: ISODate("2014-09-15T04:58:19.431Z"),
      type: "clicked"
    },
  ],
  ...
}

How do I get the count of log entries of type "clicked" from this month, only if there is not a log entry of type "emailed" this month?

In other words, I want to find out which clicks have not been sent a related email.

So, in this example, the count would be 1 since the most recent "clicked" entry doesn't have an "emailed" entry.

Note: For this use case, clicks don't have unique IDs - this is all the data that is logged.

Upvotes: 0

Views: 4796

Answers (2)

wdberkeley
wdberkeley

Reputation: 11671

Use the following aggregation pipeline:

db.click_log.aggregate([
    { "$match" : { "log.type" : { "$ne" : "emailed" } } }, // get rid of docs with an "emailed" value in log.type and docs not from this month
    { "$unwind" : "$log" }, // unwind to get log elements as separate docs
    { "$project" : { "_id" : 1, "log" : 1, "month" : { "$month" : "$log.utc_timestamp" } } },
    { "$match" : { "log" : "clicked", "month" : <# of month> } }, // get rid of log elements not from this month and that aren't type clicked
    { "$group" : { "_id" : "$_id", "count" : { "$sum" : 1 } } } // collect clicked elements from same original doc and count number
])

This will return, for each document not having "emailed" as a value of log.type, the count of elements of the array log that have log.type value clicked and with timestamp from the current month. If you want a sliding 30-day period for month, change the $match to be a range query with $gt and $lt covering the desired time period.

Upvotes: 1

Srinivas Kishore
Srinivas Kishore

Reputation: 67

You can use query something similar to below.

db.dbversitydotcom_col.aggregate([ { $unwind: “$log” }, 
  { $match: { “log.type” : “clicked”, "log.utc_timestamp" : "your required date" } }, 
   { $sort: { “Files.Size” : -1.0 } }, { $limit: 5.0 } ]).count()

Please refer to http://dbversity.com/mongodb-importance-of-aggregation-framework/ for more detailed explanation,

Upvotes: 0

Related Questions