在 MongoDB GROUP BY 中执行 HAVING 的正确方法是什么? [英] What is the correct way to do a HAVING in a MongoDB GROUP BY?

问题描述

SQL 中的这个查询是什么(查找重复项):

For what would be this query in SQL (to find duplicates):

SELECT userId, name FROM col GROUP BY userId, name HAVING COUNT(*)>1

我在 MongoDB 中执行了这个简单的查询:

I performed this simple query in MongoDB:

res = db.col.group({key:{userId:true,name:true}, 
                     reduce: function(obj,prev) {prev.count++;}, 
                     initial: {count:0}})

我添加了一个简单的 Javascript 循环来遍历结果集，并执行过滤器以查找所有计数 > 1 的字段，如下所示:

I've added a simple Javascript loop to go over the result set, and performed a filter to find all the fields with a count > 1 there, like so:

for (i in res) {if (res[i].count>1) printjson(res[i])};

除了在客户端使用 javascript 代码之外，还有更好的方法吗?如果这是最好/最简单的方法，请说是，这个问题会对某人有所帮助:)

Is there a better way to do this other than using javascript code in the client? If this is the best/simplest way, say that it is, and this question will help someone :)

推荐答案

使用 Mongo 聚合框架的新答案

经过这个问题的提问和回答，10gen 发布了带有聚合框架的 Mongodb 2.2 版.执行此查询的新最佳方法是:

New answer using Mongo aggregation framework

After this question was asked and answered, 10gen released Mongodb version 2.2 with an aggregation framework. The new best way to do this query is:

db.col.aggregate( [
   { $group: { _id: { userId: "$userId", name: "$name" },
               count: { $sum: 1 } } },
   { $match: { count: { $gt: 1 } } },
   { $project: { _id: 0, 
                 userId: "$_id.userId", 
                 name: "$_id.name", 
                 count: 1}}
] )

10gen 有一个方便的 SQL 到 Mongo 聚合转换图表值得书签.

10gen has a handy SQL to Mongo Aggregation conversion chart worth bookmarking.

这篇关于在 MongoDB GROUP BY 中执行 HAVING 的正确方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！