mongodb - 如何按两个不同的字段分组

Question

我收藏的文档结构如下所示:

    [

    {
    sender: 'A', recipient: 'B', date: New MongoDate(1), contents: 'Blablabla1'
    },

    {
    sender: 'B', recipient: 'A', date: New MongoDate(2), contents: 'Blablabla2'
    },

    {
    sender: 'A', recipient: 'C', date: New MongoDate(4), contents: 'Blablabla1'
    },

    {
    sender: 'C', recipient: 'A', date: New MongoDate(3), contents: 'Blablabla2' 
    }

    {
    sender: 'C', recipient: 'D', date: New MongoDate(3), contents: 'Blablabla2'
    }


    ]

我想按收件人或发件人分组，并从每个组中只选择一个日期最大的文档

比如我想得到这样一个结果:

[

{
_id: 'AB', sender: 'B', recipient: 'A', date: 2, contents: 'Blablabla2', count: 2
},

{
_id: 'AC', sender: 'A', recipient: 'C',  date: 4, contents: 'Blablabla1', count: 2
}

{
_id: 'CD', sender: 'C', recipient: 'D',  date: 3, contents: 'Blablabla2', count: 1
}

]

怎么做？我不知道如何按两个不同的字段分组。

Answer 1

你能试试这个聚合吗

1. $sort - 按日期排序以获取 $group 中的 $last 元素
2. $group - 按字母顺序按 $cond _id 和 $concat 分组
3. $sort - 按 _id 升序排序结果

流水线

db.col.aggregate(
    [
        {$sort : { date : 1}},
        {$group : {
            _id : { $cond : [ {$gt : ["$sender", "$recipient"]}, {$concat : ["$recipient", "$sender"]}, {$concat : ["$sender", "$recipient"]}]},
            sender : {$last : "$sender"},
            recipient : {$last : "$recipient"},
            date : {$last : "$date"},
            contents : {$last : "$contents"},
            count : {$sum : 1}
         }
        },
        {$sort : { _id : 1}},
    ]
)

结果

{ "_id" : "AB", "sender" : "B", "recipient" : "A", "date" : 2, "contents" : "Blablabla2", "count" : 2 }
{ "_id" : "AC", "sender" : "A", "recipient" : "C", "date" : 4, "contents" : "Blablabla1", "count" : 2 }
{ "_id" : "CD", "sender" : "C", "recipient" : "D", "date" : 3, "contents" : "Blablabla2", "count" : 1 }
>