如何在MongoDB中标记字段并根据日期求和

Question

需要格式化日期并每天求和，但有时a或b值不可用。最后，得到一个关于日期和金额的文档。我使用的是MongoDB 4.2。

Data Structure:
{
"data": {
"11-10-2001": {
"a": 17.281150000000001,
"b": 11.864060000000006
},
"13-10-2020": {
"b": 2.7616699999999994
},
"12-10-2001": {
"b": 4.0809599999999997
},
"09-10-2001": {
"b": 4.1286300000000005
},
"17-10-2001": {
"a": 15.140560000000123,
"b": 5.017139999999998
},
"18-10-2001": {
"b": 1.975189999999997,
"a": 7.093789999999976
}
}
}

预期输出一个包含日期和总额的文档：

{
{
day: 11-10-2001,
sum : 29.145
},
{
day: 13-10-201,
sum : 2.7616699
},
{
day: 12-10-2001,
sum  : 4.0809599999999997
},
{
day: 17-10-2001,
sum  : 20.114
},
{
day: 18-10-2001,
sum  : 9.145
}
}

Answer 1

你可以试试，

• $map在使用$objectToArray转换为数组后迭代data对象的循环
• 使用$objectToArray、$add转换为数组后，将关键字day、sum、$reduce添加到数字对象的循环中，以求和数字的值
• $unwind解构数据数组
• $replaceRoot将data对象替换为根

db.collection.aggregate([
{
$addFields: {
data: {
$map: {
input: { $objectToArray: "$data" },
in: {
day: "$$this.k",
sum: {
$reduce: {
input: { $objectToArray: "$$this.v" },
initialValue: 0,
in: { $add: ["$$this.v", "$$value"] }
}
}
}
}
}
}
},
{ $unwind: "$data" },
{ $replaceRoot: { newRoot: "$data" } }
])

游乐场

Answer 2

您可以按照以下进行操作

db.collection.aggregate([
{
$project: { data: { "$objectToArray": "$data" } }
},
{
$unwind: "$data"
},
{
"$replaceRoot": { "newRoot": "$data" }
},
{
$addFields: { v: { "$objectToArray": "$v" } }
},
{
$addFields: {
v: {
$reduce: {
input: "$v",
initialValue: 0,
in: {
$add: [ "$$this.v", "$$value" ]
}
}
}
}
},
{
$group: {
_id: null,
data: {
$push: {
day: "$k",
sum: "$v"
}
}
}
}
])

正在工作的Mongo游乐场