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

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..

CodePudding user response：

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