MongoDB:如果字段的值与条件匹配，如何计算该字段? [英] 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.

因此，在此示例中，计数将为 1，因为最近的点击"条目没有通过电子邮件发送"的条目.

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

注意:对于此用例，点击没有唯一 ID - 这是记录的所有数据.

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

推荐答案

使用以下聚合管道:

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
])

对于每个没有通过电子邮件发送"作为 log.type 值的文档，这将返回具有 的数组 log 元素的计数log.type value clicked 和当前月份的时间戳.如果您想要一个月的 30 天滑动周期，请将 $match 更改为包含 $gt 和 $lt 的范围查询所需的时间段.

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.

这篇关于MongoDB:如果字段的值与条件匹配，如何计算该字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！