MongoDB query to get the sum of all document's array field length

Question

Below is the sample document of a collection, say "CollectionA"

{
    "_id" : ObjectId("5ec3f19225701c4f7ab11a5f"),
    "workshop" : ObjectId("5ebd37a3d33055331eb4730f"),
    "participant" : ObjectId("5ebd382dd33055331eb47310"),
    "status" : "analyzed",
    "createdBy" : ObjectId("5eb7aa24d33055331eb4728c"),
    "updatedBy" : ObjectId("5eb7aa24d33055331eb4728c"),
    "results" : [ 
        {
            "analyze_by" : {
                "user_name" : "m",
                "user_id" : "5eb7aa24d33055331eb4728c"
            },
            "category_list" : [ 
                "Communication", 
                "Controlling", 
                "Leading", 
                "Organizing", 
                "Planning", 
                "Staffing"
            ],
            "analyzed_date" : ISODate("2020-05-19T14:48:49.993Z"),
        }
    ],
    "summary" : [],
    "isDeleted" : false,
    "isActive" : true,
    "updatedDate" : ISODate("2020-05-19T14:48:50.827Z"),
    "createdDate" : ISODate("2020-05-19T14:47:46.374Z"),
    "__v" : 0
}

I need to query all the documents to get the "results" array length and return a sum of all document's "results" length.

For example,

document 1 has "results" length - 5

document 2 has "results" length - 6

then output should be 11.

Can we write a query, instead of getting all, iterating and the adding the results length??

prasad_ · Accepted Answer · 2020-06-18 11:26:21Z

1

You use an aggregation grouping query with $sum and $size aggregation operators to get the total sum of array elements size for all documents in the collection.

db.collection.aggregate( [
  { 
       $group: { 
           _id: null, 
           total_count: { $sum: { $size: "$results" } }
       } 
  }
] )

Aggregation using Mongoose's Model.aggregate():

SomeModel.aggregate([
  { 
       $group: { 
           _id: null, 
           total_count: { $sum: { $size: "$results" } }
       } 
  }
]).
then(function (result) {
  console.log(result);
});

edited Jun 18, 2020 at 11:26

answered Jun 18, 2020 at 8:27

prasad_

14.4k2 gold badges27 silver badges44 bronze badges

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

1 Comment

Crazie Ash Over a year ago

Can i get this one in mongoose pls...??

Panaes · Accepted Answer · 2020-06-18 07:46:19Z

1

If I had understand clearly you would like to project the length of the result attribute. So you should check the $size operator would work for you. https://docs.mongodb.com/manual/reference/operator/aggregation/size/

answered Jun 18, 2020 at 7:46

Panaes

362 bronze badges

Comments

Ana Lava · Accepted Answer · 2020-06-18 08:10:55Z

1

You can use $group and $sum to calculate the total size of a field which contains the size of your results array. To create the field, You can use $size in $addFields to calculate the size of results in each document and put it the field. As below:

db.getCollection('your_collection').aggregate([
{
      $addFields: {
         result_length: { $size: "$results"}
      }
   },
   {
   $group: {
        _id: '',
        total_result_length: { $sum: '$result_length' }
    }
    }

])

answered Jun 18, 2020 at 8:10

Ana Lava

7871 gold badge6 silver badges14 bronze badges

Collectives™ on Stack Overflow

MongoDB query to get the sum of all document's array field length

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related