MongoDB: count the number of items in an array

Question

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.

Stennie · Accepted Answer · 2020-06-24 23:48:22Z

109

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 }

edited Jun 24, 2020 at 23:48

answered Mar 3, 2014 at 17:05

Stennie

65.7k15 gold badges155 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

xxbidiao Over a year ago

Are there any way that we can get 7 (4+3) in this case?

Stennie Over a year ago

@xxbidiao Sure, just use a $group stage which sums up the array sizes: db.mycollection.aggregate({$group: { _id: null, totalSize: { $sum: { $size: "$foo"}} }}).

maxeh Over a year ago

I had to put [ ] around {$project: { count: { $size:"$foo" }}} for it to work, see github.com/Studio3T/robomongo/issues/…

Stennie Over a year ago

@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 is free and supported by the MongoDB team, but there are many alternatives depending on your requirements.

Stennie Over a year ago

@datdinhquoc You can add an initial $match stage 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 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.

|

xlembouras · Accepted Answer · 2014-01-27 18:07:06Z

34

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.

answered Jan 27, 2014 at 18:07

xlembouras

8,3254 gold badges35 silver badges44 bronze badges

1 Comment

Alice Jarmusch Over a year ago

Awesome, helped me as well. Can you suggest how can I have an access to "total_sum" in further calculations?

Chinthaka Fernando · Accepted Answer · 2020-10-06 09:24:16Z

7

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"},
            }
        }
    ]
)

answered Oct 6, 2020 at 9:24

Chinthaka Fernando

82411 silver badges13 bronze badges

2 Comments

chovy Over a year ago

anyway to protect if the array is missing in a document?

Dushyant Bangal Over a year ago

@chovy I know you probably no longer need this, but you can add foo1:{$type:"array"} in the $match stage.

Collectives™ on Stack Overflow

MongoDB: count the number of items in an array

3 Answers 3

9 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related