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

Question

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.

If I understand correctly that log is a property on a document, I think the solution will involve $unwind. Also, I think the individual clicks should have unique _id values even if you didn't expect them to. The _id property gets added automatically to members of a subdocument array, although maybe you have suppressed this? — Semicolon
– Semicolon, Commented Nov 22, 2014 at 17:43
@Semicolon I didn't know about the _id property being automatically assigned for members of a subdocument array. It appears MongoEngine is indeed suppressing this - log is configured as a ListField and I simply append a dictionary to it. — okoboko
– okoboko, Commented Nov 22, 2014 at 17:45

wdberkeley · Accepted Answer · 2014-11-25 02:09:50Z

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.

Neo-coder · Accepted Answer · 2014-11-24 06:36:24Z

0

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,

edited Nov 24, 2014 at 6:36

Neo-coder

7,8585 gold badges36 silver badges54 bronze badges

answered Nov 22, 2014 at 17:55

Srinivas Kishore

672 silver badges3 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related