Combining columns into one in MongoDB Aggregate Framework

Question

Is it possible to group by values across multiple columns? Let's say I'm storing interactions between people by day, and keep track of from's and to's with a count as follows. ``` db.collection = [ { from : 'bob', to : 'mary', day : 1, count : 2 }, { from : 'bob', to : 'steve', day : 2, count : 1 }, { from : 'mary', to : 'bob', day : 1, count : 3 }, { from : 'mary', to : 'steve', day : 3, count : 1 }, { from : 'steve', to : 'bob', day : 2, count : 2 }, { from : 'steve', to : 'mary', day : 1, count : 1 } ] ``` This allows me to get all interactions for, lets say, `'bob'` with any one by grouping on `from:`, and summing `count:`. Now I want to get all interaction for a user, so basically group by values across `from:` and `to:`. Essentially, sum up `count:` for each name, regardless whether it was in `from:` or `to:` [UPDATE] The desired output would be: ``` [ { name : 'bob', count : 8 }, { name : 'mary', count : 7 }, { name : 'steve', count : 3 } ] ``` The easiest would be to create a…

Accepted Answer

Is it possible to group by values across multiple columns?

Yes, it's possible in MongoDB to group values across different columns.

It's very straight forward to do it via MapReduce. But it's also possible to do it with aggregation framework, even if you don't store an array of participants (if you had array of names with both participants, then it's just an $unwind, and a $group - quite simple and I think more elegant than either MapReduce or the pipeline you'd have to use with the current schema).

Pipeline that works with your schema as is:

```
db.collection.aggregate( [
{
    "$group" : {
        "_id" : "$from",
        "sum" : {
            "$sum" : "$count"
        },
        "tos" : {
            "$push" : {
                "to" : "$to",
                "count" : "$count"
            }
        }
    }
}
{ "$unwind" : "$tos" }
{
    "$project" : {
        "prev" : {
            "id" : "$_id",
            "sum" : "$sum"
        },
        "tos" : 1
    }
}
{
    "$group" : {
        "_id" : "$tos.to",
        "count" : {
            "$sum" : "$tos.count"
        },
        "prev" : {
            "$addToSet" : "$prev"
        }
    }
}
{ "$unwind" : "$prev" }
{
    "$group" : {
        "_id" : "1",
        "t" : {
            "$addToSet" : {
                "id" : "$_id",
                "c" : "$count"
            }
        },
        "f" : {
            "$addToSet" : {
                "id" : "$prev.id",
                "c" : "$prev.sum"
            }
        }
    }
}
{ "$unwind" : "$t" }
{ "$unwind" : "$f" }
{
    "$project" : {
        "name" : {
            "$cond" : [
                {
                    "$eq" : [
                        "$t.id",
                        "$f.id"
                    ]
                },
                "$t.id",
                "nobody"
            ]
        },
        "count" : {
            "$add" : [
                "$t.c",
                "$f.c"
            ]
        },
        "_id" : 0
    }
}
{ "$match" : { "name" : { "$ne" : "nobody" } } }
]);
```

On your sample input the output is:

```
{
    "result" : [
        {
            "name" : "bob",
            "count" : 8
        },
        {
            "name" : "mary",
            "count" : 7
        },
        {
            "name" : "steve",
            "count" : 5
        }
    ],
    "ok" : 1
}
```

Combining columns into one in MongoDB Aggregate Framework

Solution

Problem