MongoDb组按月汇总无法执行聚合

Question

我有一个聚合脚本，它在最后阶段产生以下输出。我现在想按账户类别分组和求和，但它告诉我，我正试图从BSON类型的EOO转换为Date——这在我的代码中随处可见。

{ 
"_id" : {
    "party_uuid" : "phildominickcompany", 
    "connection_uuid" : "5738fc661a21db15b5c45b49", 
    "account_balances_date" : ISODate("2016-06-30T10:00:00.000+0000"), 
    "object_origin_category" : "Bookkeeping", 
    "object_origin" : "Sage One"
}, 
"account_identifier" : "5010", 
"account_name" : "Cost of sales - materials", 
"account_category" : "Sales Expense", 
"account_type" : null, 
"account_value_type" : "debit", 
"account_value" : NumberInt(0)
}
{ 
"_id" : {
    "party_uuid" : "phildominickcompany", 
    "connection_uuid" : "5738fc661a21db15b5c45b49", 
    "account_balances_date" : ISODate("2016-07-31T10:00:00.000+0000"), 
    "object_origin_category" : "Bookkeeping", 
    "object_origin" : "Sage One"
}, 
"account_identifier" : "4000", 
"account_name" : "Sales Type A", 
"account_category" : "Sales Revenue", 
"account_type" : null, 
"account_value_type" : "credit", 
"account_value" : 57728.33
}
{ 
"_id" : {
    "party_uuid" : "phildominickcompany", 
    "connection_uuid" : "5738fc661a21db15b5c45b49", 
    "account_balances_date" : ISODate("2016-07-31T10:00:00.000+0000"), 
    "object_origin_category" : "Bookkeeping", 
    "object_origin" : "Sage One"
}, 
"account_identifier" : "5000", 
"account_name" : "Cost of sales - goods", 
"account_category" : "Sales Expense", 
"account_type" : null, 
"account_value_type" : "debit", 
"account_value" : NumberInt(10000)
}
{ 
"_id" : {
    "party_uuid" : "phildominickcompany", 
    "connection_uuid" : "5738fc661a21db15b5c45b49", 
    "account_balances_date" : ISODate("2016-07-31T10:00:00.000+0000"), 
    "object_origin_category" : "Bookkeeping", 
    "object_origin" : "Sage One"
}, 
"account_identifier" : "5010", 
"account_name" : "Cost of sales - materials", 
"account_category" : "Sales Expense", 
"account_type" : null, 
"account_value_type" : "debit", 
"account_value" : NumberInt(20000)
}
{ 
"_id" : {
    "party_uuid" : "phildominickcompany", 
    "connection_uuid" : "5738fc661a21db15b5c45b49", 
    "account_balances_date" : ISODate("2016-07-31T10:00:00.000+0000"), 
    "object_origin_category" : "Bookkeeping", 
    "object_origin" : "Sage One"
}, 
"account_identifier" : "6200", 
"account_name" : "Marketing", 
"account_category" : "Other Expense", 
"account_type" : null, 
"account_value_type" : "debit", 
"account_value" : NumberInt(1500)
}

我现在尝试申请的阶段如下。它应该按年份和月份对account_value求和。它产生了一个错误，告诉我我正试图从EOO到约会。

$group : {
_id: {"party_uuid" : "$party_uuid", 
  "account_category" : "$account_category",
  "account_balances_year" : {$year : "$account_balances_date"}, 
  "account_balances_month" : {$month : "$account_balances_date"}, 
  "account_category" : "$account_category",
  "account_type" : "$account_type",
  "object_origin_category" : "$object_origin_category", 
  "object_origin" : "$object_origin"},
  "month_value" : { $sum: "$account_value"}
}

谢谢，Matt

Answer 1

要按年份和月份对account_value求和，请添加以下$group管道

{
    "$group": {
        "_id": {
            "year": { "$year": "$_id.account_balances_date" },
            "month": { "$month": "$_id.account_balances_date" }
        },
        "total_account_value": { "$sum": "$account_value" }
    }
}

这将从样本输出：

/* 1 */
{
    "_id" : {
        "year" : 2016,
        "month" : 7
    },
    "total_account_value" : 89228.33
}
/* 2 */
{
    "_id" : {
        "year" : 2016,
        "month" : 6
    },
    "total_account_value" : 0
}

Answer 2

首先，您必须投影字段$account_balances_date以获得month&年份&然后按年份分组&月如下所示：

$project: {
        year: { $year: "$account_balances_date" },
       month: { $month: "$account_balances_date" },
    },
$group : {
          "_id": {
                  "account_balances_year" : $year, 
                  "account_balances_month" : $month, 
                },
                "month_value" : { $sum: "$account_value"}
    }

Alos，如果您使用以前的分组By，则必须稍微更改此查询。