如何在MongoDB聚合的最近36个月内过滤$ match中的文档 [英] How to filter documents in $match by the last 36 months in MongoDB aggregation

问题描述

我只想获取此特定字段 EffectiveDate大于36个月前的文档。我试过了：

I want to only obtain documents where this specific field "EffectiveDate" is greater than 36 months ago. I have tried:

"$match" : 
{"member.MemberEnrollmentSpans.MemberEnrollmentSpan.EffectiveDate": {$gte: 
ObjectID.createFromTime(Date.now() - 36*31*24*60*60)}}

但是它似乎不喜欢' ObjectID.createFromTime '语句，它说我的错误在'O'位置，意外字符'O'

But It doesn't seem to like the 'ObjectID.createFromTime' statement, it say my error is at the 'O' position, "Unexpected Character 'O'"

是否执行 $ match 运算符

var date = new Date();
(date.setMonth(date.getMonth() - 36));
var dateInput = date.toLocaleDateString();


db.getCollection("Members").aggregate(
    [
        { 
            "$match" : {
                "member.MemberInfo.BusinessUnitCode" : "20"
             }
        }, 
        { 
            "$match": { $expr: { $lt: [{ $toDate: dateInput }, 
"$member.MemberEnrollmentSpans.MemberEnrollmentSpan.EndDate"] } } 
        }, 
        { 
            "$unwind" : {
                "path" : 
"$member.MemberEnrollmentSpans.MemberEnrollmentSpan", 
            "preserveNullAndEmptyArrays" : false
            }
        }, 
        { 
            "$project" : {
            "EffDate" : "$member.MemberEnrollmentSpans.MemberEnrollmentSpan.EffectiveDate", 
            "PlanProduct" : "$member.MemberEnrollmentSpans.MemberEnrollmentSpan.PlanProduct", 
            "PlanProductCode" : "$member.MemberEnrollmentSpans.MemberEnrollmentSpan.PlanProductCode", 
            "BenefitPackage" : "$member.MemberEnrollmentSpans.MemberEnrollmentSpan.BenefitPackage", 
            "EndDate" : "$member.MemberEnrollmentSpans.MemberEnrollmentSpan.EndDate"
        }
    }, 
    { 
        "$group" : {
            "_id" : "$_id", 
            "items" : {
                "$addToSet" : {
                    "EffDate" : "$EffDate", 
                    "PlanProductCode" : "$PlanProductCode", 
                    "PlanProduct" : "$PlanProduct", 
                    "BenefitPackage" : "$BenefitPackage", 
                    "EndDate" : "$EndDate"
                }
            }
        }
    }
], 
{ 
    "allowDiskUse" : true
}

）;

推荐答案

尝试一下：

在您的代码中：

let date = new Date();
(date.setMonth(date.getMonth() - 36));
let dateInput = date.toLocaleDateString(); // 1/10/2017

查询1：

db.yourCollectionName.aggregate([{ $match: { $expr: { $lt: [{ $toDate: dateInput }, "$date"] } } }])

收集数据：

/* 1 */
{
    "_id" : ObjectId("5e17a6a5627ef7823644a088"),
    "date" : ISODate("2015-08-01T00:00:00.000Z")
}

/* 2 */
{
    "_id" : ObjectId("5e17a6ac627ef7823644a13f"),
    "date" : ISODate("2016-08-01T00:00:00.000Z")
}

/* 3 */
{
    "_id" : ObjectId("5e17a6c0627ef7823644a2d6"),
    "date" : ISODate("2019-12-01T00:00:00.000Z")
}

结果：

/* 1 */
{
    "_id" : ObjectId("5e17a6c0627ef7823644a2d6"),
    "date" : ISODate("2019-12-01T00:00:00.000Z")
}

（或）从此处getOlderRecordsByMins ，您可以执行此操作，两者都会得到相同的结果，但得到 36 个月到这个数字 94608103680 有点困难，这两个查询都是简单的&足够快且使用较少的运算符-但是，如果您认为通过代码获取 94608103680 更容易，则可以这样做：

(Or) as from here getOlderRecordsByMins, you can do this, both would get you same results but getting 36 months to this number 94608103680 is a bit hard, both of these queries are simple & fast enough with less operators being used - but if you think getting 94608103680 through code is easier then this go ahead with this :

查询2：

db.yourCollectionName.aggregate([
    { $match: { date: { $gt: new Date(ISODate().getTime() - 94608103680) } } }])

这篇关于如何在MongoDB聚合的最近36个月内过滤$ match中的文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！