Mongo $sum $cond with two conditions

Question

I have an aggregation query that returns the sum / total number of reviews submitted for a given location ( not the average star rating ). Reviews are scored 1 - 5 stars. This particular query groups these reviews into two categories, "internal" and "google".

I have a query that returns results that are almost what I'm looking for. However, I need to add an additional condition for internal reviews. I want to ensure that the internal reviews "stars" value exists / is not null and contains a value of at least 1. So, I was thinking adding something similar to this would work:

{ "stars": {$gte: 1} }

This is the current aggregation query:

[
      {
        $match: { createdAt: { $gte: fromDate, $lte: toDate } }
      },
      {
        $lookup: {
          from: 'branches',
          localField: 'branch',
          foreignField: '_id',
          as: 'branch'
        }
      },
      { $unwind: '$branch' },
      {
        $match: { 'branch.org_id': branchId }
      },
      {
        $group: {
          _id: '$branch.name',
          google: {
            $sum: {
              $cond: [{ $eq: ['$source', 'Google'] }, 1, 0]
            }
          },
          internal: {            
            $sum: {
              $cond: [  { $eq: ['$internal', true]}, 1, 0 ],
            },
          }
        }
      }
]

Truncated Schema:

  {
    branchId: { type: String, required: true },
    branch: { type: Schema.Types.ObjectId, ref: 'branches' },
    wouldRecommend: { type: String, default: '' }, // RECOMMENDATION ONLY
    stars: { type: Number, default: 0 }, // IF 1 - 5 DOCUMENT IS A REVIEW
    comment: { type: String, default: '' },
    internal: { type: Boolean, default: true },
    source: { type: String, required: true },
  },
  { timestamps: true }

I need to make sure that I'm not counting "wouldRecommend" recommendations in the sum of the internal reviews. Do determine if something is a review it will have a star rating of 1 or more stars. Recommendations will have a star value of 0.

How can I add the condition that ensures the internal "$stars" value is >= 1 ( greater than or equal to 1 ) ?

Using Ashh's answer I was able to form this query:

[
  {
    $lookup: {
      from: 'branches',
      localField: 'branch',
      foreignField: '_id',
      as: 'branch'
    }
  },
  { $unwind: '$branch' },
  {
    $match: {
      'branch.org_id': branchId
    }
  },
  {
    $group: {
      _id: '$branch.name',
      google: {
        $sum: {
          $cond: [{ $eq: ['$source', 'Google'] }, 1, 0]
        }
      },
      internal: {
        $sum: {
          $cond: [
            {
              $and: [{ $gte: ['$stars', 1] }, { $eq: ['$internal', true] }]
            },
            1,
            0
          ]
        }
      }
    }
  }
];

Answer 1

You can use $and with the $cond operator

{ "$group": {
  "_id": "$branch.name",
  "google": { "$sum": { "$cond": [{ "$eq": ["$source", "Google"] }, 1, 0] }},
  "internal": { "$sum": { "$cond": [{ "$eq": ["$internal", true] }, 1, 0 ] }},
  "rating": {            
    "$sum": {
      "$cond": [
        {
          "$and": [
            { "$gte": ["$stars", 1] },
            { "$eq": ["$internal", true] }
          ]
        },
        1,
        0
      ],
    }
  }
}}