how to count the size of an array object and unwind it in MongoDB

Question

I need to count the size of an array object and I also need to get the averages for each field in the array labeled raised_amount. However, MongoDB will not let me count the array size after unwinding it(duh). Mongo will not let me count the array size before unwinding either. This is for a class I am taking. Quite the challenge.

db.research.aggregate({$unwind:"$funding_rounds"},
    {"$group": {
       "_id":{"name": "$name"},
       "averageFunding" : {
           "$avg" : "$funding_rounds.raised_amount"
        }
      }
    },
    {$project: { count: { $size:"$funding_rounds" }}},
    { $sort: { averageFunding: -1 } },
    {"$limit":10})

Take out {$project: { count: { $size:"$funding_rounds" }}} and it works! However, I wouldn't have funding_round count. Try to count the rounds by themselves, and it works.

Example of data:

{
  "name": "Facebook",
  "total_money_raised": "$39.8M",
  "funding_round": [
    {
      "example": 123,
      "round_code": "a",
      "raised_amount": "1232"
    },
    {
      "example": 123,
      "round_code": "bat",
      "raised_amount": "1232"
    },
    {
      "example": 123,
      "round_code": "cat",
      "raised_amount": "1232"
    }
  ]
}

Any ideas on how to count the array size in this aggregation?

Hardik Shah · Accepted Answer · 2018-10-25 04:37:37Z

1

$size expect an array, and you $unwind array to object before counting. That's why MongoDB restrict to count size.

Try Below code:

db.getCollection('tests').aggregate([
    {
        $project: {
            _id: 1,
            name: 1,
            total_money_raised : 1,
            funding_round :1,
            size: { $size:"$funding_round" }
         }
     },
     { $unwind : "$funding_round"},
     { $group:{
          _id: "$name",
          avgFunding : {"$avg" : "$funding_round.raised_amount"},
          size: {$first : "$size"},
          totalCount : {$sum: 1}
        }
     },
     { $sort: { "avgFunding": -1 } },
     { "$limit":10 }
 ])

Output:

/* 1 */
{
    "_id" : "Facebook",
    "avgFunding" : 1232.0,
    "size" : 3,
    "totalCount" : 3.0
}

If NAME field is unique:

Another thing that I need to mention here is if your name field is unique and you just want to have the size of an array you can then unwind and then count total documents while $group as below:

db.getCollection('tests').aggregate([
     { $unwind : "$funding_round"},
     { $group:{
          _id: "$name",
          "avgFunding" : {"$avg" : "$funding_round.raised_amount"},
          size : {$sum: 1}
        }
     },
     { $sort: { "avgFunding": -1 } },
     { "$limit":10 }
 ])

Output:

/* 1 */
{
    "_id" : "Facebook",
    "avgFunding" : 1232.0,
    "size" : 3.0
}

Where size is the total count of documents that are unwound from an array.

edited Oct 25, 2018 at 4:37

answered Oct 25, 2018 at 4:28

Hardik Shah

4,2082 gold badges22 silver badges45 bronze badges

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

1 Comment

chase dougherty Over a year ago

Just so future people know in the future, it was the "if NAME field is unique" option that I used

Collectives™ on Stack Overflow

how to count the size of an array object and unwind it in MongoDB

1 Answer 1

If NAME field is unique:

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

If NAME field is unique:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related