CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Matt Lightbourn
Matt Lightbourn

Reputation: 597

MongoDb group sum by month unable to execute aggregate

I have an aggregation script which at the last stage results in the following output. I now want to group and sum by account category but it's telling me that I am somehow trying to convert from BSON type EOO to Date - which I cannot see in my code anywhere.

{ 
"_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)
}

The stage I'm trying to apply now is as follows. It is supposed to sum account_value by year and month. It's producing an error telling me I'm trying to go from an EOO to a date.

$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"}
}

Thanks, Matt

Upvotes: 1

Views: 1027

Answers (2)

chridam
chridam

Reputation: 103365

To sum account_value by year and month, add the following $group pipeline

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

This will output from the sample:

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

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

Upvotes: 2

Abhay
Abhay

Reputation: 6760

First, you have to project the field $account_balances_date to get month & year & then group by by year & month. Something like below :

$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, if you are using previously group By, You have to change this query a little bit.

Upvotes: 1

Related Questions