MongoDB Aggregation $size on Nested Field

Question

I'm trying to perform a tricky aggregation to return the size of a nested array within a document in the collection.

Here is how to re-create my sample data:

db.test.insert({
    projects: [
        {
            _id: 1,
            comments: [
                'a',
                'b',
                'c'
            ]
        },
        {
            _id: 2,
            comments: [
                'a',
                'b'
            ]
        },
        {
            _id: 3,
            comments: []
        }
    ]
})

The aggregation I would perform goes here:

db.test.aggregate([
    // enter aggregation here
])

Here is the desired output:

[{
    projects: [
        {
            _id: 1,
            comment_count: 3
        },
        {
            _id: 2,
            comment_count: 2
        },
        {
            _id: 3,
            comment_count: 0
        }
    ]
}]

I'm struggling with how to write this. If I try the following:

"projects.comment_count": {"$size": }

The result returns the size of the resulting array:

[{
    projects: [
        {
            _id: 1,
            comment_count: 3
        },
        {
            _id: 2,
            comment_count: 3
        },
        {
            _id: 3,
            comment_count: 3
        }
    ]
}]

If I try to use the $map method like this:

"projects.comment_count": { 
    "$map": { 
        "input": "$projects", 
        "as": "project", 
        "in": {
            "$size": "$$project.comments"
        } 
    } 
}

It will return an array that looks like this for each object in the array:

[{
    projects: [
        {
            _id: 1,
            comment_count: [3, 2, 0]
        },
        {
            _id: 2,
            comment_count: [3, 2, 0]
        },
        {
            _id: 3,
            comment_count: [3, 2, 0]
        }
    ]
}]

Thanks in advance!

Can't be much help to your question here, but wanted to say this is a well-worded question for a first-timer — Corbfon
– Corbfon, Commented Sep 21, 2018 at 1:32

Akrion · Accepted Answer · 2018-09-21 02:41:33Z

3

Here is an idea using $unwind, $group and then $push with $size. Finally $project to get rid of that _id:

db.collection.aggregate([
  {
    "$unwind": "$projects"
  },
  {
    $group: {
      _id: null,
      "projects": {
        $push: {
          _id: "$projects._id",
          comment_count: {
            $size: "$projects.comments"
          }
        }
      }
    }
  },
  {
    "$project": {
      "_id": 0
    }
  }
])

You can see the result here

edited Sep 21, 2018 at 2:41

answered Sep 21, 2018 at 1:56

Akrion

18.6k1 gold badge38 silver badges58 bronze badges

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

2 Comments

Israel Zinc Over a year ago

Just one typo in the code, he wants the "id" with an hyphen, so the code inside the $push command should be _id:"$projects._id".

jzkyburz Over a year ago

I ended up using most of this code. Because I have a much longer aggregation pipeline that pulls documents from multiple other collections upstream, I had to ditch that last $project and built my final output inside of the $group with reference to the top-level document using $$ROOT. Thank you!

Ashh · Accepted Answer · 2018-09-21 03:03:41Z

2

You need to specify each field inside the in argument of $map aggregation and finally use $size with the comments array.

Something like this

db.collection.aggregate([
  { "$project": {
    "projects": {
      "$map": {
        "input": "$projects",
        "in": {
          "_id": "$$this._id",
          "comment_count": {
            "$size": "$$this.comments"
          }
        }
      }
    }
  }}
])

Output

[
  {
    "projects": [
      {
        "_id": 1,
        "comment_count": 3
      },
      {
        "_id": 2,
        "comment_count": 2
      },
      {
        "_id": 3,
        "comment_count": 0
      }
    ]
  }
]

answered Sep 21, 2018 at 3:03

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Collectives™ on Stack Overflow

MongoDB Aggregation $size on Nested Field

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related