Aggregate sum AND count together not working

Question

I have a script which works in the current state but if you un-comment the count under group and try to do the count, it doesn't work, it errors, not entirely sure why. There are also additional problems mentioned below:

db.getCollection("9SP_Data").aggregate([

{"$match" : {"_id.object_category" : "revenue-transaction"}},
// include match for range of one year 
// include match for company
// maybe include extra parameters like origin_category or origin_name from customer settings for metric
    {"$unwind" :  "$line_items"},
    {"$match"  :  {"line_items.item_category":"sales-revenue"}},
    {"$group" : {
       "_id":
           {
            "company" : "$_id.company",
            "sum_by_date":  { $substrBytes: [ "$_id.transaction_date", 0, 6 ] },
            //  4 - by year
            //  6 - by month 
            //  8 - by date 
            // 10 - by hour 
            // 12 - by minute 
            "category" : "$line_items.item_category",
            "origin_category" : "$_id.object_origin_category",
            "origin_type" : "$_id.object_origin_type",
            "object_origin" : "$_id.object_origin"},
            "metric_value"  : { $sum: "$line_items.item_net_total_value" },
//                { $count: "metric_volume" }
}},
{"$project" : {
    "_id.company"               : "$_id.company",
    "_id.metric_name"           : {$literal : "revenue"},
    "_id.metric_category"       : {$literal : "sales"},
    "_id.metric_type"           : {$literal : "month"},
    "_id.metric_lookup"         : "$sum_by_date",
    "_id.object_origin_category": "$_id.origin_category",
    "_id.object_origin_type"    : "$_id.object_origin_type",
    "_id.object_origin"         : "$_id.object_origin",
    "metric_value"              : "$metric_value"
//        ,"metric_volume"             : "$metric_volume"
}}
])

I haven't used $count before and the mongoDb documentation isn't helping me.

The result I get in current form is:

{
"_id" : {
    "metric_name" : "revenue",
    "metric_category" : "sales",
    "metric_type" : "month",
    "object_origin_category" : "point-of-sale",
    "object_origin" : "vend"
},
"metric_value" : 2099.9997
}

I am not getting company, but I know why, it doesn't exist in my data set.

I'm not sure why _id.object_origin_type isn't coming through yet category is

No idea why _id.metric_lookup isn't coming out either

Not metric_volume which comes from problematic $count

Here's an example origin document used by aggregation:

{
"_id" : {
    "connection" : "cb1c4a56-1544-4e9d-a433-abb33429a300",
    "transaction_date" : 20170714020700,
    "transaction_date_utc" : "2017-07-14 02:07:00",
    "object_class" : "goods-service-transaction",
    "object_category" : "revenue-transaction",
    "object_type" : "receipt",
    "object_origin_category" : "point-of-sale",
    "object_origin_type" : "offline",
    "object_origin" : "vend",
    "transaction_status" : "CLOSED",
    "related_reference" : "41"
},
"object_creation_date" : "20181210120902",
"party_identifier" : "WALKIN",
"staff_identifier" : "02dcd191-ae2b-11e6-f485-7967ed9c6343",
"staff_name" : "uat1@9spokes.com",
"line_items" : [
    {
    "item_name" : "Dress Shirt / Polyester / Large",
    "item_system_id" : "02dcd191-ae20-11e6-f485-7967eef9a797",
    "item_identifier" : "10024",
    "item_category" : "sales-revenue",
    "item_type" : "goods-service",
    "item_quantity" : 1,
    "item_net_unit_sale_value" : 61.3636,
    "item_net_unit_discount_value" : 0,
    "item_unit_tax_value" : 6.8182,
    "item_net_total_value" : 61.3636,
    "item_total_tax_value" : 6.81818,
    "item_total_gross_value" : 68.18182
}
]
}

Thanks for your help. Matt

Answer 1

$count is Aggregation Pipeline Stage you try use it as aggregation pipeline operator. You must put $count after $group and then you count num of groups(if you this neded) or simple use somthing like this "metric_volume":{$sum:1} (after _id declaration of course) for counting grouped documents.