How do I count documents with a specific value in specific field in MongoDB?

Question

I have the following documents:

{
    timestamp: 2022-11-04T08:58:03.303+00:00
    name: Brian
    username: [email protected]
    type: Type A
}

{
    timestamp: 2022-11-04T09:20:13.564+00:00
    name: Brian
    username: [email protected]
    type: Type A
}

{
    timestamp: 2022-11-04T13:12:25.024+00:00
    name: Anna
    username: [email protected]
    type: Type A
}

{
    timestamp: 2022-11-04T05:32:58.834+00:00
    name: Max
    username: [email protected]
    type: Type B
}

{
    timestamp: 2022-11-04T03:34:23.011+00:00
    name: Jan
    username: [email protected]
    type: Type c
}

I'm gonna use this data in a timeline chart, so I've used $deinsify and $fill, to create buckets in week units.

Now I'm trying to group this data into the units of a week which I do like this:

{
    $group: {
        _id: {
            bins: {
                $dateTrunc: {
                    date: '$timestamp',
                    unit: 'week',
                    binSize: 1,
                    timezone: 'Europe/Paris',
                    startOfWeek: 'monday'
                }
            }
        }
    }
}

This works fine, but in this group I also want to count the number of distinct usernames for types A and B, and I also want to count the number of documents where type is specifically "Type A".

The goal is to get something like this:

{
    timestamp: 2022-10-30T23:00:00.000+00:00
    usernameCount: 3
    typeAOccurencies: 3
}

This is what I've tried:

I couldn't find a way to do the distinct count directly in that group so I thought that a way to do it is by adding values to an array and then in a project aggregation afterwards use $size, something like this:

{
    $match: {
        'logType': {  
            $in: [ 'Type A', 'Type B' ] 
        }
    }
},
{
    $group: {
        _id: {
            bins: {
                $dateTrunc: {
                    date: '$timestamp',
                    unit: 'week',
                    binSize: 1,
                    timezone: 'Europe/Paris',
                    startOfWeek: 'monday'
                }
            }
        },
        usernameCount: {
            $addToSet: '$username'
        },
        typeAOccurencies: {
            $push: '$type'
        },
    }
}, 
{
    $project: {
        _id: 0,
        timestamp: '$_id.bins'
        usernameCount: {
            $size: '$usernameCount'
        },
        typeAOccurencies: {
            $size: '$typeAOccurencies'
        }
    }
}

The only problem I have right now is that I don't know how to only push type fields with 'Type A' values into typeAOccurencies. Right now Type B also gets pushed, which it shouldn't..

Yong Shun · Accepted Answer · 2022-11-09 09:56:15Z

1

Updated: Work with $sum with $cond.

db.collection.aggregate([
  {
    $group: {
      _id: {
        bins: {
          $dateTrunc: {
            date: "$timestamp",
            unit: "week",
            binSize: 1,
            timezone: "Europe/Paris",
            startOfWeek: "monday"
          }
        }
      },
      usernameCount: {
        $addToSet: "$username"
      },
      typeAOccurencies: {
        $sum: {
          $cond: {
            if: {
              $eq: [
                "$type",
                "Type A"
              ]
            },
            then: 1,
            else: 0
          }
        }
      },
      typeBOccurencies: {
        $sum: {
          $cond: {
            if: {
              $eq: [
                "$type",
                "Type B"
              ]
            },
            then: 1,
            else: 0
          }
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      timestamp: "$_id.bins",
      usernameCount: {
        $size: "$usernameCount"
      },
      typeAOccurencies: 1,
      typeBOccurencies: 1
    }
  }
])

Demo @ Mongo Playground

edited Nov 9, 2022 at 9:56

answered Nov 9, 2022 at 9:22

Yong Shun

54.6k6 gold badges38 silver badges66 bronze badges

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

3 Comments

TheStranger Over a year ago

You're completely correct, but I missed out an important aspect in my example, my bad. So I've updated the OP. I can match on Type A and Type B, and the usernameCount should be both for users with Type A and Type B, but the typeAOccurencies count should only be for Type A. That's why I couldn't use match. Apologies.

Yong Shun Over a year ago

It's okay. Just confused with your previous usernameCount value. I would suggest that you need to sum by type.

Yong Shun Over a year ago

Bonus: If you look for a dynamic way without hardcode the type, you may work with group by timestamp and type too. Demo

Collectives™ on Stack Overflow

How do I count documents with a specific value in specific field in MongoDB?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related