时间戳

首页 > 解决方案 > 将两个数组元素与mongodb聚合中的条件进行比较

mongodb - 将两个数组元素与mongodb聚合中的条件进行比较

问题描述

我有文件

[{
        "id": "1",
        "base": 23,
        "dateValues": [{
            "timestamp": "2021-02-15T13:13:17.611Z",
            "value": 50
        }, {
            "timestamp": "2021-02-22T13:13:17.611Z",
            "value": 50
        }]
    },
    {
        "id": "2",
        "base": 256,
        "dateValues": [{
            "timestamp": "2021-02-01T13:13:17.611Z",
            "value": 50
        }, {
            "timestamp": "2021-02-10T13:13:17.611Z",
            "value": 50
        }]
    },
    {
        "id": "3",
        "base": 256,
        "dateValues": [{
            "timestamp": "2021-02-03T13:13:17.611Z",
            "value": 50
        }, {
            "timestamp": "2021-02-04T13:13:17.611Z",
            "value": 50
        }, {
            "timestamp": "2021-02-10T13:13:17.611Z",
            "value": 50
        }]
    },
    {
        "id": "4",
        "base": 256,
        "dateValues": [{
            "timestamp": "2021-02-03T13:13:17.611Z",
            "value": 50
        }, {
            "timestamp": "2021-02-08T13:13:17.611Z",
            "value": 50
        }]
    }
]

现在我想比较所有对象的日期。我将尽可能简单地简化逻辑..

  1. 首先，我想获取所有不同的日期集，因此这些日期将是
"2021-02-01"
"2021-02-03"
"2021-02-04"
"2021-02-08"
"2021-02-15"
"2021-02-22"
"2021-02-10"
  1. 现在我想循环上面的日期并将每个日期与每个对象 dateValues 进行比较

例如，如果我们取第一个日期“2021-02-01”，我只想要每个对象（文档）的一个日期

所以对于这个日期“2021-02-01” dateValues 将是

for id 1 no date value is there so in this case I need to take base value, which is 23
for id 2 we found date value so we will take its corresponding value, which is 50
for id 3 and 4 no date is matching so again we are taking its base value which 256 and 256
so final value comes for date "2021-02-01" is 23+50+256+256 = 585

日期“2021-02-22”的另一个示例

for id 1 we found date "2021-02-22", value is 50
for id 2 we found date "2021-02-10" value is 50
for id 3 we found date "2021-02-10" value is 50
for id 4 we found date "2021-02-08" value is 50
Final value for date "2021-02-22" is 200

我们可以在 mongo db 聚合中执行此逻辑吗？我只能得到不同的日期，但在那之后我失去了它。谁能帮我解决这个 mongodb 聚合？或者甚至可以在 mongodb 聚合中实现这个逻辑？

标签： mongodbmongodb-queryaggregation-framework

解决方案

Mongo 聚合框架有很多强大的操作符，它基本上可以归结为以正确的顺序链接阶段和操作符。

假设您有一个名为的集合中的文档st。

解释：

  • Unwind and Group 将为您提供每个日期的一份文件
  • 为每个日期查找同一集合中的所有文档
  • 在查找期间将查找中的管道应用于所有文档，查找与日期匹配的 dateValue 或返回基数。
  • 汇总记录
[
    { $unwind: "$dateValues" },
    { $group: {
        _id: "$dateValues.timestamp"
    }},
    { $lookup: {
        from: "st",
        as: "records",
        let: { date: "$_id" },
        pipeline: [
            { $project: {
                base: 1,
                dateValues: {
                    $filter: {
                        input: "$dateValues",
                        as: "dateValue",
                        cond: {
                            $eq: ["$$dateValue.timestamp", "$$date"]
                        }
                    }
                }
            }},
            { $project: {
                val: { $cond: {
                    if: {$eq: [ {$size: "$dateValues"}, 1]},
                    then: "$dateValues.0.value",
                    else: "$base"
                }}
            }},
        ],
    }},
    { $project: {
        _id: 1,
        finalValue: {
            $sum: "$records.val"
        }
    }}
]

结果：

{ "_id" : "2021-02-22T13:13:17.611Z", "finalValue" : 768 }
{ "_id" : "2021-02-01T13:13:17.611Z", "finalValue" : 535 }
{ "_id" : "2021-02-15T13:13:17.611Z", "finalValue" : 768 }
{ "_id" : "2021-02-04T13:13:17.611Z", "finalValue" : 535 }
{ "_id" : "2021-02-10T13:13:17.611Z", "finalValue" : 279 }
{ "_id" : "2021-02-03T13:13:17.611Z", "finalValue" : 279 }
{ "_id" : "2021-02-08T13:13:17.611Z", "finalValue" : 535 }

注意：请仔细检查$filter和$cond并确保它符合您的要求。

更新的管道：添加了一个 reduce 阶段

  • 过滤阶段选择所有dateValues时间戳 <= 日期。
  • reduce阶段选择其中最大dateValue的。
[
        { $unwind: "$dateValues" },
        { $group: {
            _id: "$dateValues.timestamp"
        }},
        { $lookup: {
            from: "st",
            as: "records",
            let: { date: "$_id" },
            pipeline: [
                { $project: {
                    base: 1,
                    dateValues: {
                        $filter: {
                            input: "$dateValues",
                            as: "dateValue",
                            cond: { $lte: ["$$dateValue.timestamp", "$$date"] },
                        }
                    }
                }},
                { $project: {
                    base: 1,
                    dateValues: {
                        $reduce: {
                            input: "$dateValues",
                            initialValue: null,
                                in: { $cond: {
                                    if: { $gt: ["$$this.timestamp", "$$value.timestamp"] },
                                    then: "$$this",
                                    else: "$$value"
                                }}
                        }
                    }
                }},
                { $project: {
                    val: { $cond: {
                        if: {$eq: ["$dateValues", null]},
                        then: "$base",
                        else: "$dateValues.value",
                    }}
                }},
            ],
        }},
        { $project: {
            _id: 1,
            finalValue: {
                $sum: "$records.val"
            }
        }}
]

推荐阅读