使用聚合框架在MongoDB中进行组计数

问题描述

假设我的MongoDB模式如下:

Let's say my MongoDB schema looks like this:

{car_id: "...", owner_id: "..."}

这是一个多对多的关系.例如，数据可能如下所示:

This is a many-to-many relationship. For example, the data might look like this:

+-----+----------+--------+
| _id | owner_id | car_id |
+-----+----------+--------+
|   1 |        1 |      1 |
|   2 |        1 |      2 |
|   3 |        1 |      3 |
|   4 |        2 |      1 |
|   5 |        2 |      2 |
|   6 |        3 |      4 |
|   7 |        3 |      5 |
|   8 |        3 |      6 |
|   9 |        3 |      7 |
|  10 |        1 |      1 | <-- not unique
+-----+----------+--------+

我想获取每个车主拥有的汽车数量.在SQL中，可能看起来像这样:

I want to get the number of cars owned by each owner. In SQL, this might look like:

SELECT owner_id, COUNT(*) AS cars_owned
FROM (SELECT owner_id FROM car_owners GROUP BY owner_id, car_id) AS t
GROUP BY owner_id;

在这种情况下，结果将如下所示:

In this case, the result would look like this:

+----------+------------+
| owner_id | cars_owned |
+----------+------------+
|        1 |          3 |
|        2 |          2 |
|        3 |          4 |
+----------+------------+

如何通过聚合框架使用MongoDB来完成同一件事?

How can I accomplish this same thing using MongoDB using the aggregation framework?

推荐答案

要容纳潜在的重复项，您需要使用两个$group操作:

To accommodate the potential duplicates, you need to use two $group operations:

db.test.aggregate([
    { $group: {
        _id: { owner_id: '$owner_id', car_id: '$car_id' }
    }},
    { $group: {
        _id: '$_id.owner_id',
        cars_owned: { $sum: 1 }
    }},
    { $project: {
        _id: 0,
        owner_id: '$_id',
        cars_owned: 1
    }}]
    , function(err, result){
        console.log(result);
    }
);

给出以下格式的结果:

[ { cars_owned: 2, owner_id: 10 },
  { cars_owned: 1, owner_id: 11 } ]

这篇关于使用聚合框架在MongoDB中进行组计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！