如何在Mongodb中按月份和年份范围过滤 [英] How to filter by month and year range in Mongodb

问题描述

我的文档如下所示:

<代码>{id: "abc",值:1234，月份:6，年份:2018}

如何在聚合中过滤出如下查询:

<代码>{来自:6/2017"，至:8/2018"}

解决方案

我自己搞定的:

db.bills.aggregate([{$匹配:{$expr:{$或:[{$和:[{$eq: ["$year", FROMYEAR]},{$eq: ["$year", TOYEAR]},{$gte: ["$month", FROMMONTH},{$lte: ["$month", TOMONTH},]},{$和:[{$eq: ["$year", FROMYEAR]},{$lt: ["$year", TOYEAR]},{$gte: ["$month", FROMMONTH]}]},{$和:[{$gt: ["$year", FROMYEAR]},{$eq: ["$year", TOYEAR]},{$lte: ["$month", TOMONTH]}]},{$和:[{$gt: ["$year", FROMYEAR]},{$lt: ["$year", TOYEAR]}]}]}}},{$项目:{身份证":1，月":1，年":1，_id":0}},,{$排序:{年":1，月":1}}]).toArray()

首先是从查询中提取fromYear、fromMonth、toYear、toMonth.接下来是将其设置到查询中的相应位置.有 4 个范围要匹配:

MinYear = $year = MaxYear ==========>最小月$月最大月份MinYear = $year MinMonth <= $month最小年$year = MaxYear ==========>$month <= MaxMonth最小年$年任何$月

结果如下:

<预><代码>[{"id": "BI_0010","月" : 5,年":2017},{"id": "BI_0008",月":3，年":2018},{"id": "BI_0001","月" : 8,年":2018}]

My document looks like this:

{
     id: "abc",
     value: 1234,
     month: 6,
     year: 2018
}

How can I filter out the query like the following in aggregation:

{
     from: "6/2017",
     to: "8/2018"
}

解决方案

I got it by myself:

db.bills.aggregate([
{
    $match: {
        $expr: {
            $or: [
                {
                    $and: [
                        {$eq: ["$year", FROMYEAR]},
                        {$eq: ["$year", TOYEAR]},
                        {$gte: ["$month", FROMMONTH},
                        {$lte: ["$month", TOMONTH},
                    ]
                },
                {
                    $and: [
                        {$eq: ["$year", FROMYEAR]},
                        {$lt: ["$year", TOYEAR]},
                        {$gte: ["$month", FROMMONTH]}
                    ]
                },
                {
                    $and: [
                        {$gt: ["$year", FROMYEAR]},
                        {$eq: ["$year", TOYEAR]},
                        {$lte: ["$month", TOMONTH]}
                    ]
                },
                {
                    $and: [
                        {$gt: ["$year", FROMYEAR]},
                        {$lt: ["$year", TOYEAR]}
                    ]
                }
            ]
        }
    }
},
{
    $project: {
        "id": 1,
        "month": 1,
        "year": 1,
        "_id": 0
    }
},
,
{
    $sort: {
        "year": 1,
        "month": 1
    }
}
]).toArray()

The first is to extract fromYear, fromMonth, toYear, toMonth from query. The next is to set it to corresponding position in the query. There are 4 ranges to match:

MinYear = $year = MaxYear ==========> MinMonth < $month < MaxMonth
MinYear = $year < MaxYear ==========> MinMonth <= $month
MinYear < $year = MaxYear ==========> $month <= MaxMonth
MinYear < $year < MaxYear ==========> any $month

The result looks like the following:

[
    {
        "id" : "BI_0010",
        "month" : 5,
        "year" : 2017
    },
    {
        "id" : "BI_0008",
        "month" : 3,
        "year" : 2018
    },
    {
        "id" : "BI_0001",
        "month" : 8,
        "year" : 2018
    }
]

这篇关于如何在Mongodb中按月份和年份范围过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！