MongoDB: add field with conditional number of elements

Question

I have next simplified collection

[
  {
    "key": 1,
    "array": [
      {        "check": true      },
      {        "check": false      },
      {        "check": true      }
    ]
  },
  {
    "key": 2
  }
]

I want to add field "count" with number of elements of array with "check"=true, so I expect next result

  {
    "key": 1,
    "array": [
      {        "check": true      },
      {        "check": false      },
      {        "check": true      }
    ],
    "count":2,
  },
  {
    "key": 2,
    "count": 0
  }
]

I have next query ( it is aggregation, because actually it one of stages of pipeline)

db.collection.aggregate([
  {
    "$addFields": {
      "count": {
        "$sum": {
          "$cond": {
            "if": {
              "$eq": ["$array.check",true],              
            },
            "then": 1,
            "else": 0,            
          }
        }
      },      
    }
  }
])

But I always get count=0.

Can you help me to find error in my query?

Here mongo playground

You can use the $reduce aggregation array operator to calculate the conditional count. — prasad_
– prasad_, Commented Jul 13, 2020 at 13:41
Thanks for your answers. Possibly $reduce is the best solution. I also try to understand, why my query is not working. — Ragnar
– Ragnar, Commented Jul 13, 2020 at 13:59

thammada.ts · Accepted Answer · 2020-07-13 14:11:52Z

2

Instead of using $sum, you can use $filter to filter only the array with "check"=true, then check the size of the resulting array using $size.

db.collection.aggregate([
  {
    "$addFields": {
      "count": {
        "$size": {
          "$filter": {
            "input": { "$ifNull": ["$array", []] }, // default empty array if array is does not exist
            "cond": "$$this.check" // only keep the truthy check value
          }
        }
      }
    }
  }
])

Mongo Playground

Alternatively, if you want to use $sum, you could also map the array to an array of 0 and 1 according to the check value, using $map

db.collection.aggregate([
  {
    "$addFields": {
      "count": {
        "$sum": {
          "$map": {
            "input": { "$ifNull": ["$array", []] },
            "in": {
              "$cond": ["$$this.check", 1, 0]
            }
          }
        }
      }
    }
  }
])

Mongo Playground

edited Jul 13, 2020 at 14:11

answered Jul 13, 2020 at 13:45

thammada.ts

5,2653 gold badges26 silver badges35 bronze badges

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

4 Comments

Ragnar Over a year ago

Yes, it is possible solution, thanks. But I also try to understand, why sum with cond is not working.

thammada.ts Over a year ago

Because you are using not using $sum in the $group stage. Your use of $sum accept and array expression and calculate the sum of the values in the array. You can not put conditional logic there.

prasad_ Over a year ago

You can do the aggregation with $unwind and then group with sum conditionally.

thammada.ts Over a year ago

@Ragnar I have added an alternative using $sum with $map

Tom Slabbaert · Accepted Answer · 2020-07-13 13:45:12Z

1

Here is how to achieve this using $reduce

db.collection.aggregate([
  {
    "$addFields": {
      "count": {
        "$sum": {
          $reduce: {
            input: "$array",
            initialValue: 0,
            in: {
              $sum: [
                "$$value",
                {
                  $cond: [
                    "$$this.check",
                    1,
                    0
                  ]
                }
              ]
            }
          }
        }
      }
    }
  }
])

Mongo Playground

answered Jul 13, 2020 at 13:45

Tom Slabbaert

22.5k10 gold badges35 silver badges45 bronze badges

Comments

prasad_ · Accepted Answer · 2020-07-13 15:54:59Z

Using $reduce aggregation array expression operator,

db.test.aggregate( [
  { 
      $addFields: { 
          count: { 
              $reduce: { 
                  input: { $ifNull: [ "$array", [] ] }, 
                  initialValue: 0, 
                  in: { 
                      $cond: [ { $eq: [ "$$this.check", true ] }, 
                               { $add: [ "$$value", 1 ] }, 
                               "$$value" 
                      ] 
                  }
              }
          }
      }
  }
] )

gets a result as follows:

{
        "_id" : ObjectId("5f0c7b691c7c98bb49fd2b50"),
        "key" : 1,
        "array" : [
                {
                        "check" : true
                },
                {
                        "check" : false
                },
                {
                        "check" : true
                }
        ],
        "count" : 2
}
{ "_id" : ObjectId("5f0c7b691c7c98bb49fd2b51"), "key" : 2, "count" : 0 }

Collectives™ on Stack Overflow

MongoDB: add field with conditional number of elements

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related