Mongodb sum the size of array fields

Question

so i have a bunch of simple documents like

{

  "foos": [
    ObjectId("5105862f2b5e30877c685c58"),
    ObjectId("5105862f2b5e30877c685c57"),
    ObjectId("5105862f2b5e30877c685c56"),
  ],

  "typ": "Organisation",

}

and i want to find out the overall size of associated foos to documents of type "Organisation"

so i have this aggregate query

db.profil.aggregate(
   [
   {
    $match:{
     "typ":"Organisation"
    }
   },
      {
         $project: {
            fooos: { $size: "$foos" }
         }
      }
   ]
)

this returns the count of all foos for each document

like :

{ "_id" : ObjectId("50e577602b5e05e74b38a6c8"), "foooos" : 1 }
{ "_id" : ObjectId("51922170975a09f363e3eef5"), "foooos" : 3 }
{ "_id" : ObjectId("51922170975a09f363e3eef8"), "foooos" : 2 }
{ "_id" : ObjectId("5175441d975ae346a3a8dff2"), "foooos" : 0 }
{ "_id" : ObjectId("5192216f975a09f363e3eee9"), "foooos" : 2 }
{ "_id" : ObjectId("5192216f975a09f363e3eeeb"), "foooos" : 3 }
{ "_id" : ObjectId("5192216f975a09f363e3eee4"), "foooos" : 2 }
{ "_id" : ObjectId("5192216f975a09f363e3eee6"), "foooos" : 2 }
{ "_id" : ObjectId("5192216f975a09f363e3eedb"), "foooos" : 2 }
{ "_id" : ObjectId("51922174975a09f363e3ef4a"), "foooos" : 1 }
{ "_id" : ObjectId("5192216f975a09f363e3eee1"), "foooos" : 1 }
{ "_id" : ObjectId("5192216e975a09f363e3eed7"), "foooos" : 2 }
{ "_id" : ObjectId("5192216f975a09f363e3eeee"), "foooos" : 3 }

is there some query that would return the summed up count for foos of all documents ?

i played arround with $sum but dont know how to combine with my query, i only do get syntax errors, it would be cool to know if this is possible

chridam · Accepted Answer · 2018-09-07 10:33:43Z

65

Include the $group operator pipeline stage after the $project step as follows:

db.profil.aggregate([
   { "$match":{ "typ": "Organisation" } },
   { "$project": {
         "fooos": { "$size": "$foos" }
   } },
   { "$group": {
       "_id": null,
       "count": {
           "$sum": "$fooos"
       }
   } }
])

This will group all the input documents from the previous $project stage and applies the accumulator expression $sum on the fooos field within the group to get the total (using your last example):

This can also be done by-passing the $project pipeline as:

db.profil.aggregate([
   { "$match": { "typ": "Organisation" } },
   { "$group": {
       "_id": null,
        "count": {
            "$sum": { "$size": "$foos" }
        }
    } }
])

Output

/* 0 */
{
    "result" : [ 
        {
            "_id" : null,
            "count" : 24
        }
    ],
    "ok" : 1
}

edited Sep 7, 2018 at 10:33

answered May 14, 2015 at 11:47

chridam

104k26 gold badges246 silver badges243 bronze badges

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

5 Comments

Markus W Mahlberg Over a year ago

Please note that the _id field isn't necessary.

chridam Over a year ago

@MarkusWMahlberg Thanks for noting that.

john Smith Over a year ago

wow, thanks a lot it works like a charme ! i needed to include "foos":{$ne:null} to the $match query to put it to work

chridam Over a year ago

@johnSmith Nice one! Glad it all worked out for you in the end

robynico Over a year ago

More longer than : aggregate([ { "$match": { "typ": "Organisation" } }, { $group: { _id: "", "total": {$sum: {$size: "$foos"}}}}]

The Hero of Canton · Accepted Answer · 2016-02-17 09:54:25Z

17

I know this is an old question but you can bypass project altogether if you want, so how about this?

db.profil.aggregate([
{
    "$match":{ "typ": "Organisation" }
},
{
    "$group":
    {
        "_id": null,
        "count":
        {
            "$sum": { "$size": "$foos" }
        }
    }
}])

The output remains the same and it seems it's (slightly) faster.

answered Feb 17, 2016 at 9:54

The Hero of Canton

1911 silver badge3 bronze badges

Collectives™ on Stack Overflow

Mongodb sum the size of array fields

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related