MongoDB complex select count group by function

Question

我有一个名为'my_email '的集合，其中存储了电子邮件地址:

[
    { email:"[e-mail]"},
    { email:"[e-mail]"},
    { email:"[e-mail]"},
]

和我试着得到使用最多的10个主机名…

[
{host: "gmail.com", count: 1000},
{host: "yahoo.com", count: 989}, ...
]

如果我有MySQL，我将执行以下查询:

SELECT substr(email,locate('@',email)+1,255) AS host,count(1) AS count
FROM my_emails 
WHERE email like '%@%' 
GROUP BY substr(email,locate('@',email)+1,255)
ORDER BY count(1) DESC 
LIMIT 10

我怎么能做mongodb ?我尝试没有结果像这样:

db.my_emails.aggregate([ { $group : {_id : "$host", count : { $sum : 1 }}}]);

我不知道如何使$host值不添加一个新的属性到我的记录

Answer 1

MongoDB不提供任何像locate这样的操作符，但您可以使用 .mapReduce 来做到这一点:

db.collection.mapReduce(
    function() {
        emit(this.email.substr(this.email.indexOf('@') + 1), 1);  
    }, 
    function(host, count) { 
        return Array.sum(count) ; }, 
    { out: "hosts" } 
)

然后db.hosts.find().sort({ 'value': -1 }).limit(10)返回前10名主机名:

{ "_id" : "yahoo.com", "value" : 2 }
{ "_id" : "gmail.com", "value" : 1 }

Answer 2

另一种解决方法是通过在模式中引入另一个字段来修改数据结构，该字段仅保存电子邮件地址的域值。这可以通过使用 bulk API操作来实现批量更新，该操作可以提供更好的写响应，即关于更新期间实际发生的事情的有用信息:

var bulk = db.my_emails.initializeUnorderedBulkOp(),    
    count = 0;
db.my_emails.find().forEach(function(doc) {
    var domain = doc.email.replace(/.*@/, ""),
        update = { domain: domain };
    bulk.find({ "_id": doc._id }).updateOne({
        "$set": update
    })
    count++;
    if (count % 1000 == 0) {            
        bulk.execute();
        bulk = db.my_emails.initializeUnorderedBulkOp();
    }
})
if (count % 1000 != 0) { bulk.execute(); } 

来自样本的批量更新响应:

BulkWriteResult({
    "writeErrors" : [ ],
    "writeConcernErrors" : [ ],
    "nInserted" : 0,
    "nUpserted" : 0,
    "nMatched" : 3,
    "nModified" : 3,
    "nRemoved" : 0,
    "upserted" : [ ]
})

更新之后，对集合db.my_emails.find().pretty()的查询将产生:

{
    "_id" : ObjectId("561618af645a64b1a70af2c5"),
    "email" : "[e-mail]",
    "domain" : "gmail.com"
}
{
    "_id" : ObjectId("561618af645a64b1a70af2c6"),
    "email" : "[e-mail]",
    "domain" : "yahoo.com"
}
{
    "_id" : ObjectId("561618af645a64b1a70af2c7"),
    "email" : "[e-mail]",
    "domain" : "yahoo.com"
}

现在，拥有域字段将使聚合框架更容易通过 $group 管道中的 $sum 操作符为您提供主机计数。下面的管道操作将返回期望的结果:

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

:

{ "_id" : "yahoo.com", "count" : 2 }
{ "_id" : "gmail.com", "count" : 1 }