mongoDB 遍历文档的键和求和值

Question

我是 MongoDb 的新手。话虽如此，我的 csv 文件数据如下，是关于每年的开支。

{"Name": "Aruba",
   "Code": "ABW",
   "Type": "Country",
   "IndicatorName": "Military_expenditure",
   "1900": 0,
   "1961": 1,
   "1962": 0,
   "1963": 0,
   "1964": 0,
   "1965": 0,
   "1966": 0,
   "1967": 0,
   "1968": 0,
   "1969": 0
}, {
   "Name": "Afghanistan",
   "Code": "AFG",
   "Type": "Country",
   "IndicatorName": "Military_expenditure",
   "1900": 0,
   "1961": 100,
   "1962": 0,
   "1963": 0,
   "1964": 0,
   "1965": 0,
   "1966": 0,
   "1967": 0,
   "1968": 0,
   "1969": 0
}

但是，我需要求和

1. 年度总开支，即

Aruba=1 >> (1900=0 + 1961=1......+ 1969=0} Afghanistan = 100 >> (1900=0 + 1961=100......+ 1969=0}

1. 国家总支出=101

Aruba (1900=0 + 1961=1......+ 1969=0} + Afghanistan(1900=0 + 1961=100......+ 1969=0}

谁能帮忙用MongoDb做上面的计算

但是，我已经编写了查询以获取年度总和

db.MiltryExpenditure.aggregate([

 { $match: { "Type":"Country" } },

 {$group:{_id : null,
 1969: { $sum: { "$toDouble":"$1969" }}
, _id : null,
 1960: { $sum: { 

"$toDouble":"$1960" }},
}}
])

但我不知道如何获得国家/地区的总和，以及如果他们有一种标准化方法来获得国家/地区明智的总和，那将不胜感激。

请帮忙...

Answer 1

$objectToArray允许您转换您的 $$ROOT对象转换为键和值的数组。那你可以申请$filter在那个数组上只得到代表年份的那些对。一旦数据集被限制为年份，您就可以运行 $unwind为了执行 $group每年分别:

db.collection.aggregate([
    {
        $project: {
            _id: 0,
            years: { 
                $filter: { 
                    input: { $objectToArray: "$$ROOT" }, 
                    cond: { $and: [ { $gte: [ "$$this.k", "1900" ] }, { $lte: [ "$$this.k", "2020" ] } ] } 
                } 
            }
        }
    },
    {
        $unwind: "$years"
    },
    {
        $group: {
            _id: "$years.k",
            total: { $sum: "$years.v" }
        }
    },
    {
        $sort: { _id: 1 }
    }
])

Mongo Playground

按国家分组更容易，你可以运行$sum两次(通过 doc 总结所有年份，然后在 $group 中):

db.collection.aggregate([
    {
        $project: {
            _id: 0,
            Name: 1,
            years: { 
                $filter: { 
                    input: { $objectToArray: "$$ROOT" }, 
                    cond: { $and: [ { $gte: [ "$$this.k", "1900" ] }, { $lte: [ "$$this.k", "2020" ] } ] } 
                } 
            }
        }
    },
    {
        $group: {
            _id: "$Name",
            total: { $sum: { "$sum": "$years.v" } }
        }
    }
])

Mongo Playground (2)

编辑:如果每个国家/地区只有一个文档，则可以缩短第二个查询(您可以去掉 $group):

db.collection.aggregate([
    {
        $project: {
            _id: 0,
            Name: 1,
            Total: {
                $let: {
                    vars: { years: { 
                        $filter: { 
                            input: { $objectToArray: "$$ROOT" }, 
                            cond: { $and: [ { $gte: [ "$$this.k", "1900" ] }, { $lte: [ "$$this.k", "2020" ] } ] } 
                        } 
                    } },
                    in: { $sum: "$$years.v" }
                }
            }
        }
    }
])