MongoDB aggregate group array to key : sum value

Question

Hello I am new to mongodb and trying to convert objects with different types (int) into key value pairs.

I have collection like this:

{
    "_id" : ObjectId("5372a9fc0079285635db14d8"),
    "type" : 1,
    "stat" : "foobar"
},
{
    "_id" : ObjectId("5372aa000079285635db14d9"),
    "type" : 1,
    "stat" : "foobar"
},
{
    "_id" : ObjectId("5372aa010079285635db14da"),
    "type" : 2,
    "stat" : "foobar"
},{
    "_id" : ObjectId("5372aa030079285635db14db"),
    "type" : 3,
    "stat" : "foobar"
}

I want to get result like this:

{
    "type1" : 2, "type2" : 1, "type3" : 1,
    "stat" : "foobar"
}

Currently trying aggregation group and then push type values to array

db.types.aggregate(
    {$group : {
        _id : "$stat",
        types : {$push : "$type"}
    }}
)

But don't know how to sum different types and to convert it into key values

/* 0 */
{
    "result" : [ 
        {
            "_id" : "foobar",
            "types" : [ 
                1, 
                2, 
                2, 
                3
            ]
        }
    ],
    "ok" : 1
}

if you want to be able to map values to keys, vote for this jira ticket jira.mongodb.org/browse/SERVER-5947 as it's not currently possible. — Asya Kamsky
– Asya Kamsky, Commented May 14, 2014 at 14:49

Neil Lunn · Accepted Answer · 2014-05-14 02:49:24Z

6

For your actual form, and therefore presuming that you actually know the possible values for "type" then you can do this with two $group stages and some use of the $cond operator:

db.types.aggregate([
    { "$group": {
         "_id": {
             "stat": "$stat",
             "type": "$type"
         },
         "count": { "$sum": 1 }
    }},
    { "$group": {
        "_id": "$_id.stat",
        "type1": { "$sum": { "$cond": [
            { "$eq": [ "$_id.type", 1 ] },
            "$count",
            0
        ]}},
        "type2": { "$sum": { "$cond": [
            { "$eq": [ "$_id.type", 2 ] },
            "$count",
            0
        ]}},
        "type3": { "$sum": { "$cond": [
            { "$eq": [ "$_id.type", 3 ] },
            "$count",
            0
        ]}}
    }}
])

Which gives exactly:

{ "_id" : "foobar", "type1" : 2, "type2" : 1, "type3" : 1 }

I actually prefer the more dynamic form with two $group stages though:

db.types.aggregate([
    { "$group": {
         "_id": {
             "stat": "$stat",
             "type": "$type"
         },
         "count": { "$sum": 1 }
    }},
    { "$group": {
        "_id": "$_id.stat",
        "types": { "$push": {
            "type": "$_id.type",
            "count": "$count"
        }}
    }}
])

Not the same output but functional and flexible to the values:

{
    "_id" : "foobar",
    "types" : [
            {
                    "type" : 3,
                    "count" : 1
            },
            {
                    "type" : 2,
                    "count" : 1
            },
            {
                    "type" : 1,
                    "count" : 2
            }
    ]
}

Otherwise if you need the same output format but need the flexible fields then you can always use mapReduce, but it's not exactly the same output.

db.types.mapReduce(
    function () {

        var obj = { };

        var key = "type" + this.type;
        obj[key] = 1;

        emit( this.stat, obj );

    },
    function (key,values) {

        var obj = {};

        values.forEach(function(value) {
            for ( var k in value ) {
                if ( !obj.hasOwnProperty(k) )
                    obj[k] = 0;
                obj[k]++;
            }
        });

        return obj;

    },
    { "out": { "inline": 1 } }
)

And in typical mapReduce style:

   "results" : [
            {
                    "_id" : "foobar",
                    "value" : {
                            "type1" : 2,
                            "type2" : 1,
                            "type3" : 1
                    }
            }
    ],

But those are your options

edited May 14, 2014 at 2:49

answered May 14, 2014 at 2:27

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Borislav Gizdov Over a year ago

I used your suggestion for condition, which makes the result in the desired format, and works perfectly My collection is little bit more complex and I will have big number of documents. Are these conditions in the query will make performance issues?

Neil Lunn Over a year ago

@PoisoneR Was there something wrong with the answer here? There are a few approaches shown but there is nothing to "dynamically" rename these fields with the exception of the mapReduce method which is not exactly the same due to how that works at this time. But the ways shown are the present ways to do this or at least get close to the result. So it is not as if another answer exists, but this question still sits with unaccepted answers. Why? Performance is relative, but the aggregation framework is faster than mapReduce. Modern MongoDB has options to allow for memory usage.

Borislav Gizdov Over a year ago

I was forget to mark answer as accepted. It is working. Just wanted to know how the performance is affected with several $group and $project stages in the pipeline. Thanks.

John Petrone · Accepted Answer · 2014-05-14 00:53:37Z

1

Is this close enough for you?

{ "_id" : "foobar", "types" : [ { "type" : "type3", "total" : 1 }, { "type" : "type2", "total" : 1 }, { "type" : "type1", "total" : 2 } ] }

The types are in an array, but it seems to get you the data you are looking for. Code is:

db.types.aggregate(
    [{$group : {
        _id : "$stat",
        types : {$push : "$type"}
    }},
    {$unwind:"$types"},
    {$group: {
        _id:{stat:"$_id",
        types: {$substr: ["$types", 0, 1]}},
        total:{$sum:1}}},
    {$project: {
        _id:0,
        stat:"$_id.stat",
        type: { $concat: [ "type", "$_id.types" ] },
        total:"$total" }},
    {$group: {
        _id: "$stat",
        types: { $push: { type: "$type", total: "$total" } } }}
   ]
)

answered May 14, 2014 at 0:53

John Petrone

27.5k6 gold badges68 silver badges69 bronze badges

3 Comments

Neil Lunn Over a year ago

The result is achievable, even though I prefer the array form. But mostly here, why all the stages? I added a response to show that whatever the approach you really only need two.

John Petrone Over a year ago

agreed, wasn't done with it - had played around with $concat to match the format the OP desired and was waiting for feedback before optimizing.

Borislav Gizdov Over a year ago

I think this result will not be good for my problem. I want to show table data in html template, and this result will represent one row.

Collectives™ on Stack Overflow

MongoDB aggregate group array to key : sum value

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related