在MongoDB Aggregate Framework中将列合并为一 [英] Combining columns into one in MongoDB Aggregate Framework

问题描述

是否可以将多个列中的值进行分组?

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 }
]

这使我可以通过分组from:并求和count:来获得与任何'bob'的所有交互.

This allows me to get all interactions for, lets say, 'bob' with any one by grouping on from:, and summing count:.

现在，我希望获得用户的所有互动，因此基本上是按from:和to:上的值进行分组.本质上，将每个名称的count:求和，无论它是在from:还是to:

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]

所需的输出将是:

[
    { name : 'bob',   count : 8 },
    { name : 'mary',  count : 7 },
    { name : 'steve', count : 3 }
]

最简单的方法是创建一个新列names:并在其中存储from:和to:，然后存储$unwind，但这似乎很浪费.

The easiest would be to create a new column names: and store from: and to: inside, then $unwind, but that seems wasteful.

有任何提示吗?

谢谢

推荐答案

是否可以将多个列中的值进行分组?

Is it possible to group by values across multiple columns?

是的，在MongoDB中可以将不同列中的值分组.

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

通过MapReduce进行操作非常简单.但是，即使您不存储一组参与者，也可以使用聚合框架来完成此工作(如果您同时拥有两个参与者的名称数组，那么这只是一个$ unwind和一个$ group-非常简单，我认为比MapReduce或您必须在当前架构中使用的管道更优雅).

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
}

这篇关于在MongoDB Aggregate Framework中将列合并为一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！