Mongodb counts all array elements in all objects matching the criteria

Question

I have a collection, which is a log of activity on such objects:

{ "_id" : ObjectId("55e3fd1d7cb5ac9a458b4567"), "object_id" : "1", "activity" : [ { "action" : "test_action", "time" : ISODate("2015-08-31T00:00:00.000Z") }, { "action" : "test_action", "time" : ISODate("2015-08-31T00:00:22.000Z") } ] } { "_id" : ObjectId("55e3fd127cb5ac77478b4567"), "object_id" : "2", "activity" : [ { "action" : "test_action", "time" : ISODate("2015-08-31T00:00:00.000Z") } ] } { "_id" : ObjectId("55e3fd0f7cb5ac9f458b4567"), "object_id" : "1", "activity" : [ { "action" : "test_action", "time" : ISODate("2015-08-30T00:00:00.000Z") } ] } 

If I execute the following query:

 db.objects.find({ "createddate": {$gte : ISODate("2015-08-30T00:00:00.000Z")}, "activity.action" : "test_action"} }).count() 

it returns the number of documents containing "test_action" (3 in this set), but I need to get the counter of all test_games (4 in this set). How to do it?

Answer 1

The most “efficient” way to do this is to skip $unwind altogther and just $group to count. Essentially, filter arrays get $size results $sum :

 db.objects.aggregate([ { "$match": { "createddate": { "$gte": ISODate("2015-08-30T00:00:00.000Z") }, "activity.action": "test_action" }}, { "$group": { "_id": null, "count": { "$sum": { "$size": { "$setDifference": [ { "$map": { "input": "$activity", "as": "el", "in": { "$cond": [ { "$eq": [ "$$el.action", "test_action" ] }, "$$el", false ] } }}, [false] ] } } } }} ]) 

Future releases of MongoDB will have $filter , which makes this a lot easier:

 db.objects.aggregate([ { "$match": { "createddate": { "$gte": ISODate("2015-08-30T00:00:00.000Z") }, "activity.action": "test_action" }}, { "$group": { "_id": null, "count": { "$sum": { "$size": { "$filter": { "input": "$activity", "as": "el", "cond": { "$eq": [ "$$el.action", "test_action" ] } } } } } }} ]) 

Using $unwind forces documents to de-normalize and efficiently create a copy to write to the array. Where possible, you should avoid this because of too high a cost. Filtering and counting array entries per document is much faster. As a simple pipeline $match and $group compared to many stages.

Answer 2

You can do this using aggregation:

 db.objects.aggregate([ {$match: {"createddate": {$gte : ISODate("2015-08-30T00:00:00.000Z")}, {"activity.action" : "test_action"}}}, {$unwind: "$activity"}, {$match: {"activity.action" : "test_action"}}}, {$group: {_id: null, count: {$sum: 1}}} ]) 

This will produce a result, for example:

 { count: 4 }