MongoDB $reduce(aggregation) 将嵌套文档在数组中的总和分组并按组计数

Question

MongoDB 聚合框架查询:$group、$project、$addFields 和 $reduce。

用例:我在集合中有多个带有嵌套文档数组的文档，需要一个结果分组依据和每个组项目的总和作为累积量。此外，在年份(日期)上设置匹配参数，如果年份匹配，则只有该年份文档应该分组并返回体积(嵌套文档数组)的总和。

以下是集合中的文档:

{
    "_id": "1",
    "LSD": {
        "name": "TDL 05",
        "LSDNumber": "031"
    },
    "POD": [{           
            "Volume": 35.40,
            "VolUnit": "m3"
        },
        {           
            "Volume": 20.75,
            "VolUnit": "m3"
        },
        {
            "Volume": 15,
            "VolUnit": "m3"
        }
    ],
     "createdon": {
        "$date": "2014-08-02T18:49:17.000Z"
    }
},
{
    "_id": "2",
    "LSD": {
        "name": "Stock Watering",
        "LSDNumber": "01"
    },

    "POD": [{
            "Volume": 105,
            "VolUnit": "m3"
        },
        {
            "Volume": 70,
            "VolUnit": "m3"
        },
        {
            "Volume": 35,
            "VolUnit": "m3"
        }
    ],
     "createdon": {
        "$date": "2014-08-02T18:49:17.000Z"
    }
},
{
    "_id": "3",
    "LSD": {
        "name": "TDL 30 Stock Water",
        "LSDNumber": "030"
    },

    "POD": [{
        "Volume": 87,
        "VolUnit": "m3"
    }],
     "createdon": {
        "$date": "2019-08-02T18:49:17.000Z"
    }
},
{
    "_id": "4",
    "LSD": {
        "name": "TDL 30 Stock Water",
        "LSDNumber": "030"
    },
    "POD": [{
        "Volume": 25.12,
        "VolUnit": "m3"
    }],
     "createdon": {
        "$date": "2019-08-02T18:49:17.000Z"
    }
},
{
    "_id": "5",
    "LSD": {
        "name": "TDL 05",
        "LSDNumber": "031"
    },
    "POD": [
        {
            "Volume": 21,
            "VolUnit": "m3"
        }
    ],
     "createdon": {
        "$date": "2014-08-02T18:49:17.000Z"
    }
}

我有一个查询(C# 驱动程序 2.0)，按“LSD.LSDNumber”和“POD.Volume”的总和分组。这里没有添加匹配参数。这很好用。

查询:

{
    aggregate([{
        "$group": {
            "_id": "$LSD.LSDNumber",            
            "doc": {
                "$push": "$POD"
            },
            "data": {
                "$first": "$$ROOT"
            }
        }
    }, {
        "$addFields": {
            "LSDNumber": "$_id",            
            "GroupByDocCount": {
                "$size": "$doc"
            },
            "Cumulative": {
                "$reduce": {
                    "input": "$doc",
                    "initialValue": [],
                    "in": {
                        "$concatArrays": ["$$value", "$$this"]
                    }
                }
            }
        }
    }, {
        "$project": {
            "LSDNumber": 1,
            "GroupByDocCount": 1,           
            "CumulativeVol": {
                "$sum": "$Cumulative.Volume"
            }
        }
    }])
}

结果如下。

{    
    "LSDNumber":"031",
    "GroupByDocCount": 2,
    "CumulativeVol": 92.15
},
{    
    "LSDNumber":"030",
    "GroupByDocCount": 2,
    "CumulativeVol": 112.12
},
{    
    "LSDNumber":"01",
    "GroupByDocCount": 1,
    "CumulativeVol": 210
}

但是，我想按年份(在“createdon”)日期以及分组依据(LSD.LSDNumber)和体积总和(POD.Volume)获取文档匹配。例如，如果年份是 2014 年，则结果应如下所示。

{    
    "LSDNumber":"031",
    "GroupByDocCount": 2,
    "CumulativeVol": 92.15,
    "Year": 2014
},
{    
    "LSDNumber":"01",
    "GroupByDocCount": 1,
    "CumulativeVol": 210,
    "Year": 2014
}

我正在尝试的查询始终不返回任何内容。

{
    aggregate([{
        "$project": {
            "LSDNumber": 1,
            "GroupByDocCount": 1,
            "CumulativeVol": {
                "$sum": "$Cumulative.Volume"
            },
            "year": {
                "$year": "$data.createdon"
            }
        }
    }, {
        "$match": {
            "year": 2014
        }
    }, {
        "$group": {
            "_id": "$LSD.LSDNumber",
            "year": {
                "$first": "$year"
            },
            "doc": {
                "$push": "$POD"
            },
            "data": {
                "$first": "$$ROOT"
            }
        }
    }, {
        "$addFields": {
            "LSDNumber": "$_id",
            "yearCreate": "$year",
            "GroupByDocCount": {
                "$size": "$doc"
            },
            "Cumulative": {
                "$reduce": {
                    "input": "$doc",
                    "initialValue": [],
                    "in": {
                        "$concatArrays": ["$$value", "$$this"]
                    }
                }
            }
        }
    }])
}

这里出了什么问题。任何帮助将不胜感激!!

Answer 1

您可以在 $addField 管道中添加 Year 变量，然后添加 $match。

{
    "$group": {
        "_id": "$LSD.LSDNumber",            
        "doc": {
            "$push": "$POD"
        },
        "data": {
            "$first": "$$ROOT"
        }
    }
}, {
    "$addFields": {
        "LSDNumber": "$_id",            
        "GroupByDocCount": {
            "$size": "$doc"
        },
        "Cumulative": {
            "$reduce": {
                "input": "$doc",
                "initialValue": [],
                "in": {
                    "$concatArrays": ["$$value", "$$this"]
                }
            }
        },
        "Year": {
            "$year": "$data.createdon"
        }
    }
}, {
    "$match" : {"Year" : 2014}
}, {
    "$project": {
        "LSDNumber": 1,
        "GroupByDocCount": 1,           
        "CumulativeVol": {
            "$sum": "$Cumulative.Volume"
        },
        "Year" : "$Year"
    }
}

=== 结果 ===

/* 1 */
{
    "_id" : "01",
    "LSDNumber" : "01",
    "GroupByDocCount" : 1,
    "CumulativeVol" : 210,
    "Year" : 2014
}

/* 2 */
{
    "_id" : "031",
    "LSDNumber" : "031",
    "GroupByDocCount" : 2,
    "CumulativeVol" : 92.15,
    "Year" : 2014
}