Group and count using aggregation framework

Question

I'm trying to group and count the following structure:

[{
    "_id" : ObjectId("5479c4793815a1f417f537a0"),
    "status" : "canceled",
    "date" : ISODate("2014-11-29T00:00:00.000Z"),
    "offset" : 30,
    "devices" : [ 
        {
            "name" : "Mouse",
            "cost" : 150,
        }, 
        {
            "name" : "Keyboard",
            "cost" : 200,
        }
    ],
},
{
    "_id" : ObjectId("5479c4793815a1f417d557a0"),
    "status" : "done",
    "date" : ISODate("2014-10-20T00:00:00.000Z"),
    "offset" : 30,
    "devices" : [ 
        {
            "name" : "LCD",
            "cost" : 150,
        }, 
        {
            "name" : "Keyboard",
            "cost" : 200,
        }
    ],
}
,
{
    "_id" : ObjectId("5479c4793815a1f417f117a0"),
    "status" : "done",
    "date" : ISODate("2014-12-29T00:00:00.000Z"),
    "offset" : 30,
    "devices" : [ 
        {
            "name" : "Headphones",
            "cost" : 150,
        }, 
        {
            "name" : "LCD",
            "cost" : 200,
        }
    ],
}]

I need group and count something like that:

 "result" : [ 
        {
            "_id" : {
                "status" : "canceled"
            },
            "count" : 1
        }, 
        {
            "_id" : {
                "status" : "done"
            },
            "count" : 2
        },
    totaldevicecost: 730,

    ],
    "ok" : 1
}

My problem in calculating cost sum in subarray "devices". How to do that?

Answer 1

It seems like you got a start on this but you got lost on some of the other concepts. There are some basic truths when working with arrays in documents, but's let's start where you left off:

db.sample.aggregate([
    { "$group": {
        "_id": "$status",
        "count": { "$sum": 1 }
    }}
])

So that is just going to use the $group pipeline to gather up your documents on the different values of the "status" field and then also produce another field for "count" which of course "counts" the occurrences of the grouping key by passing a value of 1 to the $sum operator for each document found. This puts you at a point much like you describe:

{ "_id" : "done", "count" : 2 }
{ "_id" : "canceled", "count" : 1 }

That's the first stage of this and easy enough to understand, but now you need to know how to get values out of an array. You might then be tempted once you understand the "dot notation" concept properly to do something like this:

db.sample.aggregate([
    { "$group": {
        "_id": "$status",
        "count": { "$sum": 1 },
        "total": { "$sum": "$devices.cost" }
    }}
])

But what you will find is that the "total" will in fact be 0 for each of those results:

{ "_id" : "done", "count" : 2, "total" : 0 }
{ "_id" : "canceled", "count" : 1, "total" : 0 }

Why? Well MongoDB aggregation operations like this do not actually traverse array elements when grouping. In order to do that, the aggregation framework has a concept called $unwind. The name is relatively self-explanatory. An embedded array in MongoDB is much like having a "one-to-many" association between linked data sources. So what $unwind does is exactly that sort of "join" result, where the resulting "documents" are based on the content of the array and duplicated information for each parent.

So in order to act on array elements you need to use $unwind first. This should logically lead you to code like this:

db.sample.aggregate([
    { "$unwind": "$devices" },
    { "$group": {
        "_id": "$status",
        "count": { "$sum": 1 },
        "total": { "$sum": "$devices.cost" }
    }}
])

And then the result:

{ "_id" : "done", "count" : 4, "total" : 700 }
{ "_id" : "canceled", "count" : 2, "total" : 350 }

But that isn't quite right is it? Remember what you just learned from $unwind and how it does a de-normalized join with the parent information? So now that is duplicated for every document since both had two array member. So while the "total" field is correct, the "count" is twice as much as it should be in each case.

A bit more care needs to be taken, so instead of doing this in a single $group stage, it is done in two:

db.sample.aggregate([
    { "$unwind": "$devices" },
    { "$group": {
        "_id": "$_id",
        "status": { "$first": "$status" },
        "total": { "$sum": "$devices.cost" }
    }},
    { "$group": {
        "_id": "$status",
        "count": { "$sum": 1 },
        "total": { "$sum": "$total" }
    }}
])

Which now gets the result with correct totals in it:

{ "_id" : "canceled", "count" : 1, "total" : 350 }
{ "_id" : "done", "count" : 2, "total" : 700 }

Now the numbers are right, but it is still not exactly what you are asking for. I would think you should stop there as the sort of result you are expecting is really not suited to just a single result from aggregation alone. You are looking for the total to be "inside" the result. It really doesn't belong there, but on small data it is okay:

db.sample.aggregate([
    { "$unwind": "$devices" },
    { "$group": {
        "_id": "$_id",
        "status": { "$first": "$status" },
        "total": { "$sum": "$devices.cost" }
    }},
    { "$group": {
        "_id": "$status",
        "count": { "$sum": 1 },
        "total": { "$sum": "$total" }
    }},
    { "$group": {
        "_id": null,
        "data": { "$push": { "count": "$count", "total": "$total" } },
        "totalCost": { "$sum": "$total" }
    }}
])

And a final result form:

{
    "_id" : null,
    "data" : [
            {
                    "count" : 1,
                    "total" : 350
            },
            {
                    "count" : 2,
                    "total" : 700
            }
    ],
    "totalCost" : 1050
}

But, "Do Not Do That". MongoDB has a document limit on response of 16MB, which is a limitation of the BSON spec. On small results you can do this kind of convenience wrapping, but in the larger scheme of things you want the results in the earlier form and either a separate query or live with iterating the whole results in order to get the total from all documents.

You do appear to be using a MongoDB version less than 2.6, or copying output from a RoboMongo shell which does not support the latest version features. From MongoDB 2.6 though the results of aggregation can be a "cursor" rather than a single BSON array. So the overall response can be much larger than 16MB, but only when you are not compacting to a single document as results, shown for the last example.

This would be especially true in cases where you were "paging" the results, with 100's to 1000's of result lines but you just wanted a "total" to return in an API response when you are only returning a "page" of 25 results at a time.

Anyhow, that should give you a reasonable guide on how to get the type of results you are expecting from your common document form. Remember $unwind in order to process arrays, and generally $group multiple times in order to get totals at different grouping levels from your document and collection groupings.