在聚合中使用动态值 [英] Using a Dynamic Value in Aggregation

问题描述

我有一个这样的文档结构:

I have a document structure like this:

{
    "_id" : ObjectId("59d7cd63dc2c91e740afcdb"),
    "dateJoined": ISODate("2014-12-28T16:37:17.984Z"),
    "dateActivated": ISODate("2015-02-28T16:37:17.984Z"), 
    "enrolled" : [
        { "month":-10, "enrolled":'00'},
        { "month":-9, "enrolled":'00'},
        { "month":-8, "enrolled":'01'},
        //other months
        { "month":8, "enrolled":'11'},
        { "month":9, "enrolled":'11'},
        { "month":10, "enrolled":'00'}
    ]
}

已注册的月"值是相对于dateJoin而言的，其范围是从-X到预填充的+ X.

"month" value in enrolled is relative to dateJoined that range from -X to +X that is pre-populated.

我想为每个满足条件的子文档(例如激活前5个月和激活后2个月")计算登记值为'01'的文档数.所有子文档项目都必须符合条件才能计为1.[是的，可以在激活前进行注册:)]

I would like to count number of document with enrolled value of '01' for every sub document that satisfies condition - like "5 months before activating and 2 months after activating". All sub document items must match the condition to count as 1. [Yes, it is possible to enroll before activating :)]

由于月份值不是基于dateActivated的，因此我应该能够为每个文档动态计算该值.

As the month value is not based on dateActivated, I should be able to dynamically calculate this for every document.

我正在尝试使用MongoDB聚合框架，但不确定如何动态进行.

I am trying to use MongoDB aggregation framework but not sure how to dynamically.

db.getCollection("enrollments").aggregate(
    { $match:{ //matching condition }},
    { $project: {
            enrollments: {
                $filter: {
                    input: "$enrolled",
                    as: "enrollment",
                    cond: {
                        $eq: ['$$enrolled.enroll', '01']
                        //how can I check for month value here?
                    }
                }
            }
    }}
)

推荐答案

通常考虑的是，此处要考虑"month"值的范围，如果该范围大于"之前" -5个月，并且<少于" "enrolled"数组条目中记录的"+2月".

The general ask here is to include the range for the "month" values in consideration where it is "greater than" the -5 months "before" and "less than" the +2 months "after" as recorded within the "enrolled" array entries.

问题在于，由于这些值是基于"dateJoined"的，因此需要通过"dateJoined"与"dateActivated"之间的正确间隔进行调整.这样可以使表达式有效:

The problem is that since these values are based on "dateJoined", they need to be adjusted by the correct interval between the "dateJoined" and the "dateActivated". This makes the expression effectively:

monthsDiff = (yearActivated - yearJoined)*12 + (monthActivated - monthJoined)

where month >= ( startRange + monthsDiff ) and month <= ( endRange + monthsDiff )
and enrolled = "01"

或逻辑表示的表示范围之间的月份，根据加入和激活之间的月份差值进行调整"..

如注释中所述，您首先需要在此处将这些日期值存储为BSON Date，而不是将它们存储在当前的字符串"值中.完成此操作后，您可以应用以下汇总来计算与提供的日期之间的差额，并在进行计数之前从数组中过滤调整后的范围:

As stated in comment, the very first thing you need to to here is to store those date values as a BSON Date as opposed to their present apparent "string" values. Once that is done, you can then apply the following aggregation to calculate the difference from the supplied dates and filter the adjusted range accordingly from the array before counting:

var rangeStart = -5,
    rangeEnd = 2;

db.getCollection('enrollments').aggregate([
  { "$project": {
    "enrollments": {
      "$size": {
        "$filter": {
          "input": "$enrolled",
          "as": "e",
          "cond": {
            "$let": {
              "vars": {
                "monthsDiff": {
                  "$add": [
                    { "$multiply": [
                      { "$subtract": [
                        { "$year": "$dateActivated" },
                        { "$year": "$dateJoined" }
                      ]},
                      12
                    }},
                    { "$subtract": [
                      { "$month": "$dateActivated" },
                      { "$month": "$dateJoined" }
                    ]}
                  ]
                }
              },
              "in": {
                "$and": [
                  { "$gte": [ { "$add": [ rangeStart, "$$monthsDiff" ] }, "$$e.month" ] },
                  { "$lte": [ { "$add": [ rangeEnd, "$$monthsDiff" ] }, "$$e.month" ] },
                  { "$eq": [ "$$e.enrolled", "01" ] }
                ]
              }
            }
          } 
        }
      }
    }
  }}
])

因此，这将相同的 $filter 应用于您尝试过的数组，但现在也考虑了要过滤的月份范围内的调整值.

So this applies the same $filter to the array which you were attempting, but now takes into account the adjusted values on the range of months to filter by as well.

为了便于阅读，我们应用了 $let 允许计算在变量中为$$monthsDiff获得的公共值.这是使用 $year 和 $month 从日期中提取这些数值

To make this easier to read we apply $let which allows calculation of the common value obtained for $$monthsDiff as implemented in a variable. Here is where the expression explained originally is applied, using $year and $month to extract those numeric values from the dates as stored.

使用其他数学运算符 $add ， $subtract 和 $gte 和

Using the additional mathematical operators $add, $subtract and $multiply you can calculate both the difference in months and also later apply to adjust the "range" values in the logical conditions with $gte and $lte.

最后，由于$filter发出仅包含符合条件的条目的数组，因此为了进行计数"，我们应用

Finally, because $filter emits an array of only the entries matching the conditions, in order to "count" we apply $size which returns the length of the "filtered" array, which is the "count" of matches.

根据您的预期目的，还可以在 $sum 作为 $group 蓄能器，如果真的是意图的话.

Depending on your intended purpose the whole expression can also be provided in argument to $sum as a $group accumulator, if then was indeed the intention.

这篇关于在聚合中使用动态值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！