CODEWITHSUNDEEP
mongodb
khinester
khinester

Reputation: 3520

mongodb count sub-document and list totals

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;

which would return:

+---------+------+
| 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)

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

My collection something like

> 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 }

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?

any advice much appreciated.

Upvotes: 3

Views: 15178

Answers (2)

JohnnyHK
JohnnyHK

Reputation: 311855

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'}}}
])

Output:

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

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

Upvotes: 14

Ferox
Ferox

Reputation: 461

Your id condition is blank and you do not have a where clause. I think this would be more like your sql query. Note that in case you have user_id in your users collection then you should replace $_id with $user_id

db.users.aggregate( [

{ $match: { dub: { $gt: 20 } } },
 {
  $group: {
     _id: "$_id",
     count: { $sum: 1 }
   }
 }
])

Upvotes: 0

Related Questions