$sum string values with $cond in array MongoDB

user_9677975 0
Viewed 291

I am trying to $sum some values that match the next condition.

My collection:

{
    _id: 1,
    items: [ 
    {
        name: 'item1',
        status: 'On'
    },
    {
        name: 'item2',
        status: 'On'
    },
    {
        name: 'item3',
        status: 'Off'
    }]
}

I need to $sum all items that have status 'On'

This is what i am trying:

     {
        $addFields: {
            itemsOn: { "$sum": { "$cond": [{ if: { $eq: ["$items.status", 'On'] } }, { then: 1 }, { else: 0 }] } },
        }
    }

But it is not working, any help? thanks in advance

mongodb
1 Answers
  • $filter to filter items on the base of condition, $size to get total count of returned elements from filter,
db.collection.aggregate([
  {
    $addFields: {
      itemOn: {
        $size: {
          $filter: {
            input: "$items",
            cond: { $eq: ["$$this.status", "On"] }
          }
        }
      }
    }
  }
])

Playground

Second option using $reduce,

  • $reduce to iterate loop of items array, check condition if status match then sum with value
db.collection.aggregate([
  {
    $addFields: {
      itemOn: {
        $reduce: {
          input: "$items",
          initialValue: 0,
          in: {
            $cond: [
              { $eq: ["$$this.status", "On"] },
              { $sum: ["$$value", 1] },
              "$$value"
            ]
          }
        }
      }
    }
  }
])

Playground

user_89871280
Related