63

I have a collection where every document in the collection has an array named foo that contains a set of embedded documents. Is there currently a trivial way in the MongoDB shell to count how many instances are within foo? something like:

db.mycollection.foos.count() or db.mycollection.foos.size()?

Each document in the array needs to have a unique foo_id and I want to do a quick count to make sure that the right amount of elements are inside of an array for a random document in the collection.

randombits
  • 47,058
  • 76
  • 251
  • 433

3 Answers3

98

In MongoDB 2.6, the Aggregation Framework has a new array $size operator you can use:

> db.mycollection.insert({'foo':[1,2,3,4]})
> db.mycollection.insert({'foo':[5,6,7]})

> db.mycollection.aggregate([{$project: { count: { $size:"$foo" }}}])
{ "_id" : ObjectId("5314b5c360477752b449eedf"), "count" : 4 }
{ "_id" : ObjectId("5314b5c860477752b449eee0"), "count" : 3 }
Stennie
  • 63,885
  • 14
  • 149
  • 175
  • 4
    Are there any way that we can get 7 (4+3) in this case? – xxbidiao Mar 09 '18 at 18:30
  • 11
    @xxbidiao Sure, just use a `$group` stage which sums up the array sizes: `db.mycollection.aggregate({$group: { _id: null, totalSize: { $sum: { $size: "$foo"}} }})`. – Stennie Mar 09 '18 at 21:43
  • I had to put `[ ]` around `{$project: { count: { $size:"$foo" }}}` for it to work, see https://github.com/Studio3T/robomongo/issues/1519#issuecomment-441348191 – maxeh Jun 24 '20 at 16:40
  • @Max Either syntax should work in the `mongo` shell (I just tested in the 4.2 shell), so your bug appears specific to Robo3T. However, passing the aggregation pipeline as an array in the shell [`aggregate()` helper](https://docs.mongodb.com/manual/reference/method/db.collection.aggregate/) is the preferred approach in modern versions of MongoDB so you can include additional options as a second parameter. Note that the original answer is from 2014 and there have been many changes in MongoDB since then ;-). – Stennie Jun 24 '20 at 23:55
  • 1
    @Max I'd also suggest looking for a more actively updated tool than Robo3T. Each Robo3T release embeds a specific version of the `mongo` shell which may cause unexpected issues when used with mismatched MongoDB server release versions (for example, significantly older or newer). Most admin tools use the MongoDB API for broader cross-version compatibility. [MongoDB Compass](http://mongodb.com/compass) is free and supported by the MongoDB team, but there are many alternatives depending on your requirements. – Stennie Jun 24 '20 at 23:56
  • Thanks for those infos ;) – maxeh Jun 25 '20 at 10:35
  • if there are undefined arrays.then it will be counted as 0 doing this -> db.collection.aggregate([{$project: { count: { $size: { "$ifNull": [ "$foo", [] ] } } } }]) – ns15 Jun 30 '20 at 17:58
  • but i need to `find` first, before getting sizes, how should it be? – Dee Feb 16 '21 at 15:14
  • 1
    @datdinhquoc You can add an initial [`$match` stage](https://docs.mongodb.com/manual/reference/operator/aggregation/match/) to your aggregation pipeline to filter documents before applying the projection. Without any other stages, this aggregation query would process all documents in the collection. Modern versions of MongoDB (3.4+) also have an [`$addFields` stage](https://docs.mongodb.com/manual/reference/operator/aggregation/addFields/) which can be useful to create output including existing fields from the input documents as well as newly added fields like a count of array items. – Stennie Feb 18 '21 at 19:36
  • tks, using `$match` now – Dee Feb 19 '21 at 01:36
32

if you are on a recent version of mongo (2.2 and later) you can use the aggregation framework.

db.mycollection.aggregate([
  {$unwind: '$foo'},
  {$group: {_id: '$_id', 'sum': { $sum: 1}}},
  {$group: {_id: null, total_sum: {'$sum': '$sum'}}}
])

which will give you the total foos of your collection.

Omitting the last group will aggregate results per record.

xlembouras
  • 8,215
  • 4
  • 33
  • 42
5

Using Projections and Groups

db.mycollection.aggregate(
    [
        {
            $project: {
                _id:0,
                foo_count:{$size:"$foo"},
            }
        }, 
        {
            $group: {
                foo_total:{$sum:"$foo_count"},
            }
        }
    ]
)

Multiple child array counts can also be calculated this way

db.mycollection.aggregate(
    [
        {
            $project: {
                _id:0,
                foo1_count:{$size:"$foo1"},
                foo2_count:{$size:"$foo2"},
            }
        }, 
        {
            $group: {
                foo1_total:{$sum:"$foo1_count"},
                foo2_total:{$sum:"$foo2_count"},
            }
        }
    ]
)
Chinthaka Fernando
  • 744
  • 8
  • 12