mongodb聚合框架组由两个字段组成 [英] mongodb aggregation framework group by two fields
问题描述
我使用汇总和管道查询我的数据库,有两个单独的查询:
$ groups_q = array $ b'$ group'=> array(
'_id'=>'$ group_name',
'total_sum'=> array('$ sum'=> 1)
)
);
$ statuses_q = array(
'$ group'=> array(
'_id'=>'$ user_status',
'total_sum'=> ; array('$ sum'=> 1)
)
);
$ data ['statuses'] = $ this-> mongo_db-> aggregate('users',$ statuses_q);
$ data ['groups'] = $ this-> mongo_db-> aggregate('users',$ group_q);
我得到了我想要的:
数组
(
[statuses] => Array
(
[result] =&
[0] => Array
(
[_id] =>非活动
[total_sum] => 2
)
b [1] => Array
(
[_id] => Active
[total_sum] => 5
)
)
[ok] => 1
)
[groups] => Array
(
[result] =&
(
[0] => Array
(
[_id] =>会计
[total_sum] => 1
)
[1] => Array
(
[_id] =>管理员
[total_sum] => 2
)
[2] => Array
(
[_id] => Rep
[total_sum] => 1
)
)
[ok] > 1
)
)
我不想查询我的数据库两次。有没有更好的方法来做呢?
如何使用一个查询完成它?应该使用$ project运算符吗?
您不能使用单个 aggregate code>使用所需的结果格式进行两个分组计数。一旦数据被分组,第一次您不再需要创建第二个计数所需的细节。
直接的方法是做两个查询,因为你已经
想要的选择> 在一个聚合查询中获取信息,您可以对这两个字段进行分组,然后在应用程序代码中进行一些操作。在组 _id
中有两个字段,结果将是group_name和状态的每个组合。 mongo
shell:
db.users.aggregate b {$ group:{
_id:{group_name:$ group_name,status:$ status},
'total_sum':{$ sum:1}
}}
)
这看起来不是特别有效,并且适用于一些复杂的应用程序代码,
如果您只想要每个组的唯一名称而不是名称+计数,您可以使用 $ addToSet
在单个组中。
另一个明显的替代方法是在应用程序代码中进行分组。单独执行 find()
只投影 group_name
和状态
字段,并在迭代结果时构建计数数组。
I'm querying my database using aggregation and pipeline, with two separate queries:
$groups_q = array(
'$group' => array(
'_id' => '$group_name',
'total_sum' => array('$sum' => 1)
)
);
$statuses_q = array(
'$group' => array(
'_id' => '$user_status',
'total_sum' => array('$sum' => 1)
)
);
$data['statuses'] = $this->mongo_db->aggregate('users',$statuses_q);
$data['groups'] = $this->mongo_db->aggregate('users',$groups_q);
And I'm getting what I want:
Array
(
[statuses] => Array
(
[result] => Array
(
[0] => Array
(
[_id] => Inactive
[total_sum] => 2
)
[1] => Array
(
[_id] => Active
[total_sum] => 5
)
)
[ok] => 1
)
[groups] => Array
(
[result] => Array
(
[0] => Array
(
[_id] => Accounting
[total_sum] => 1
)
[1] => Array
(
[_id] => Administrator
[total_sum] => 2
)
[2] => Array
(
[_id] => Rep
[total_sum] => 1
)
)
[ok] => 1
)
)
I don't want to query my database twice. Is there is a better way to do it? How can I accomplish it with one query? Should I use $project operator?
You can't use a single aggregate()
to do two grouped counts with your desired result format. Once the data has been grouped the first time you no longer have the details needed to create the second count.
The straightforward approach is to do two queries, as you are already doing ;-).
Thoughts on alternatives
If you really wanted to get the information in one aggregation query you could group on both fields and then do some manipulation in your application code. With two fields in the group _id
, results are going to be every combination of group_name and status.
Example using the mongo
shell :
db.users.aggregate(
{ $group: {
_id: { group_name: "$group_name", status: "$status" },
'total_sum': { $sum: 1 }
}}
)
That doesn't seem particularly efficient and lends itself to some convoluted application code because you have to iterate the results twice to get the expected groupings.
If you only wanted the unique names for each group instead of the names + counts, you could use $addToSet
in a single group.
The other obvious alternative would be to do the grouping in your application code. Do a single find()
projecting only the group_name
and status
fields, and build up your count arrays as you iterate the results.
这篇关于mongodb聚合框架组由两个字段组成的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!