mongodb count子文档并列出总计 [英] mongodb count sub-document and list totals

问题描述

在mysql中，我有一个查询，例如:

In mysql, I have a query like:

mysql> SELECT user_id, count(user_id) as dup FROM addressbook GROUP BY user_id HAVING dup>20 ORDER BY dup;

将返回:

+---------+------+
| user_id | dup  |
+---------+------+
|    3052 |   21 |
|     996 |   23 |
|      46 |   25 |
|    2709 |   26 |
|    1756 |   28 |
|      43 |   30 |
|     224 |   30 |
|      98 |   32 |
|     289 |   35 |
|     208 |   40 |
|     888 |   43 |
|    4974 |   44 |
|      31 |   46 |
|     166 |   65 |
|    4560 |   99 |
|      85 |  112 |
|     280 |  124 |
|      27 |  166 |
|    2582 |  304 |
|      45 |  476 |
|    3830 |  932 |
|     232 | 1514 |
+---------+------+
22 rows in set (0.01 sec)

当我尝试在MongoDB中复制相同的内容时，我无法正确地表述它！

When I try to duplicate the same in MongoDB, I am unable to correctly formulate it!

我的收藏类似

> db.users.find({ }).pretty();
{
    "_id" : ObjectId("540c83f9d901f28b921a328c"),
    "providers" : [
        "local"
    ],
    "loginAttempts" : 0,
    "company" : ObjectId("540c83f9d901f28b921a328a"),
    "group" : "company-admin",
    "firstName" : "Desmond",
    "emailVerified" : true,
    "addressBook" : [
        {
            "company" : "emilythepemily",
            "contact" : "Miss e m a Hudson",
            "address" : ObjectId("540c83f9d901f28b921a328d")
        },
        {
            "company" : "Hughes",
            "contact" : "Liam P Hughes",
            "address" : ObjectId("540c83f9d901f28b921a328e")
        },
...

这是我到目前为止所拥有的:

Here is what I have so far:

> db.users.aggregate({ $unwind : "$addressBook" }, { $group: { _id: '',count: { $sum: 1 }}})
{ "result" : [ { "_id" : "", "count" : 6705 } ], "ok" : 1 }

但是这给了我所有地址簿条目的总数，我如何返回每个用户记录的总数并按照mysql输出列出?

but this gives me a total of all addressBook entries, how do i return the total for each user record and listed as per the mysql output?

任何值得赞赏的建议.

推荐答案

通过使用

You can directly get the number of elements in the addressBook array field of each user by using $size:

db.users.aggregate([
    {$project: {_id: 1, count: {$size: '$addressBook'}}}
])

输出:

{
    "result" : [ 
        {
            "_id" : ObjectId("540c83f9d901f28b921a328c"),
            "count" : 2
        }
    ],
    "ok" : 1
}

请注意，$size运算符是在MongodB 2.6中引入的.

Note that the $size operator was introduced in MongodB 2.6.

这篇关于mongodb count子文档并列出总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！