MongoDB组和以ID为键的总和 [英] MongoDB group and sum with id as key

问题描述

是否可以将聚合函数的结果获取为key:count?

Is it possible to to get the result of the aggregate function as key:count?

示例:

我有以下汇总查询:

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

所以结果显示为:

{ "_id" : "moderator", "count" : 469 }
{ "_id" : "superadmin", "count" : 1 }
{ "_id" : "user", "count" : 2238 }
{ "_id" : "admin", "count" : 11 }

这样就可以了，但是有一种方法(也许使用$project)使结果看起来像这样(即以role作为键，以count作为值):

So that's all fine, but is there a way (perhaps using $project) of making results appear like this (i.e. with the role as the key and the count as the value):

{ "moderator": 469 }
{ "superadmin": 1 }
{ "user": 2238 }
{ "admin": 11 }

很明显，我可以通过用JS对结果进行后处理来做到这一点，但是我的目标是直接通过聚合函数来做到这一点.

I could do that obviously by post-processing the result with JS, but my goal is to do that directly via the aggregate function.

推荐答案

在MongoDb 3.6和更高版本中，您可以利用

With MongoDb 3.6 and newer, you can leverage the use of $arrayToObject operator and a $replaceRoot pipeline to get the desired result. You would need to run the following aggregate pipeline:

db.users.aggregate([
    { 
        "$group": {
            "_id": { "$toLower": "$role" },
            "count": { "$sum": 1 }
        }
    },
    { 
        "$group": {
            "_id": null,
            "counts": {
                "$push": {
                    "k": "$_id",
                    "v": "$count"
                }
            }
        }
    },
    { 
        "$replaceRoot": {
            "newRoot": { "$arrayToObject": "$counts" }
        } 
    }    
])

---

对于旧版本， 运算符，位于 $group 流水线步骤可以有效地根据角色字段值评估计数.您的总体聚合管道可以按以下方式构造以产生所需格式的结果:

---

For older versions, the $cond operator in the $group pipeline step can be used effectively to evaluate the counts based on the role field value. Your overall aggregation pipeline can be constructed as follows to produce the result in the desired format:

db.users.aggregate([    
    { 
        "$group": { 
            "_id": null,             
            "moderator_count": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$role", "moderator" ] }, 1, 0 ]
                }
            },
            "superadmin_count": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$role", "superadmin" ] }, 1, 0 ]
                }
            },
            "user_count": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$role", "user" ] }, 1, 0 ]
                }
            },
            "admin_count": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$role", "admin" ] }, 1, 0 ]
                }
            } 
        }  
    },
    {
        "$project": {
            "_id": 0, 
            "moderator": "$moderator_count",
            "superadmin": "$superadmin_count",
            "user": "$user_count",
            "admin": "$admin_count"
        }
    }
])

---

从注释线索开始，如果您事先不知道角色并且想动态创建管道数组，请运行

---

From the comments trail, if you don't know the roles beforehand and would like to create the pipeline array dynamically, run the distinct command on the role field. This will give you an object that contains a list of the distinct roles:

var result = db.runCommand ( { distinct: "users", key: "role" } )
var roles = result.values;
printjson(roles); // this will print ["moderator", "superadmin", "user",  "admin"]

现在给出上面的列表，您可以通过创建一个对象来组装管道，该对象将使用JavaScript的

Now given the list above, you can assemble your pipeline by creating an object that will have its properties set using JavaScript's reduce() method. The following demonstrates this:

var groupObj = { "_id": null },
    projectObj = { "_id": 0 }

var groupPipeline = roles.reduce(function(obj, role) { // set the group pipeline object 
    obj[role + "_count"] = {
        "$sum": {
            "$cond": [ { "$eq": [ "$role", role ] }, 1, 0 ]
        }
    };
    return obj;
}, groupObj );

var projectPipeline = roles.reduce(function(obj, role) { // set the project pipeline object 
    obj[role] = "$" + role + "_count";
    return obj;
}, projectObj );

在最终的聚合管道中将这两个文档用作:

Use these two documents in your final aggregation pipeline as:

db.users.aggregate([groupPipeline, projectPipeline]);

查看下面的演示.

var roles = ["moderator", "superadmin", "user",  "admin"],
	groupObj = { "_id": null },
	projectObj = { "_id": 0 };

var groupPipeline = roles.reduce(function(obj, role) { // set the group pipeline object 
	obj[role + "_count"] = {
		"$sum": {
			"$cond": [ { "$eq": [ "$role", role ] }, 1, 0 ]
		}
	};
	return obj;
}, groupObj );

var projectPipeline = roles.reduce(function(obj, role) { // set the project pipeline object 
	obj[role] = "$" + role + "_count";
	return obj;
}, projectObj );

var pipeline = [groupPipeline, projectPipeline]

pre.innerHTML = JSON.stringify(pipeline, null, 4);

<pre id="pre"></pre>

这篇关于MongoDB组和以ID为键的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！