Mongoose - filter matched documents and assign the resultant length to a field

Question

I have this collection(some irrelevant fields were omitted for brevity):

clients: {
  userId: ObjectId,
  clientSalesValue: Number,
  currentDebt: Number,  
}

Then I have this query that matches all the clients for a specific user, then calculates the sum of all debts and sales and put those results in a separate field each of them:

await clientsCollection.aggregate([
  {
    $match: { userId: new ObjectId(userId) }
  },
  {
    $group: {
      _id: null,
      totalSalesValue: { $sum: '$clientSalesValue' },
      totalDebts: { $sum: '$currentDebt' },
    }
  },
  {
    $unset: ['_id']
  }
]).exec();

This works as expected, it returns an array with only one item which is an object, but now I need to also include in that resultant object a field for the amount of debtors, that is for the amount of clients that have currentDebt > 0, how can I do that is the same query? is it possible?

PD: I cannot modify the $match condition, it need to always return all the clients for the corresponding users.

Does this answer your question? [Group and count with condition](https://stackoverflow.com/questions/30169115/group-and-count-with-condition) — Joe, Jan 13 '23 at 00:21

score 0 · Accepted Answer · answered Jan 13 '23 at 01:30

To include a count of how many matching documents have a positive currentDebt, you can use the $sum and $cond operators like so:

await clientsCollection.aggregate([
  {
    $match: { userId: new ObjectId(userId) }
  },
  {
    $group: {
      _id: null,
      totalSalesValue: { $sum: '$clientSalesValue' },
      totalDebts: { $sum: '$currentDebt' },
      numDebtors: {
        $sum: {
          $cond: [{ $gt: ['$currentDebt', 0] }, 1, 0]
        }
      }, 
    }
  },
  {
    $unset: ['_id']
  }
]).exec();

Mongoose - filter matched documents and assign the resultant length to a field

1 Answers1