2

I am trying to sum across a field within an aggregate pipeline where the field may not exist. Otherwise, the return should be zero. This is my code so far:

admits = [
    {'$match': {'meta.State': item['state'],'meta.County': item['county'], 'meta.first_seen': date}},
    {'$group': {'_id': {'item': '$item'}, 'admissions': {'$ifNull': [{'$sum': 1}, 0]}}},
]

This does not work, because calling $sum within an $ifNull raises a unary operator exception:

pymongo.errors.OperationFailure: The $ifNull accumulator is a unary operator
OJT
  • 887
  • 1
  • 10
  • 26

1 Answers1

2

pymongo.errors.OperationFailure: The $ifNull accumulator is a unary operator

The <accumulator> operator must be one of the following accumulator operators: accumulator-operator, and $ifNull operator is not one of them,

The $sum operator must be in root if you want to sum,

The usage of $ifNull is:

Evaluates an expression and returns the value of the expression if the expression evaluates to a non-null value. If the expression evaluates to a null value, including instances of undefined values or missing fields, returns the value of the replacement expression.

So $ifNull will not fulfil your requirement,

You can try $cond operator to check if field type is missing then then 0 otherwise 1,

{
  '$group': {
    '_id': {'item': '$item'}, 
    'admissions': {
      $sum: {
        $cond: [{ $eq: [{ $type: "$admissions" }, "missing"] }, 0, 1]
      }
    }
  }
},
turivishal
  • 34,368
  • 7
  • 36
  • 59
  • I'm not sure this achieves 0s in output... [{'_id': {'item': None}, 'admissions': 3}] [{'_id': {'item': None}, 'admissions': 4}] [] [{'_id': {'item': None}, 'admissions': 7}] [{'_id': {'item': None}, 'admissions': 5}] <-- on the date with zero admissions, it returns an empty list – OJT Apr 27 '21 at 14:36
  • For a given date t, there may be no documents with meta.first_seen: t – OJT Apr 27 '21 at 14:40
  • This is perhaps not the cleanest fix, but I can just try-except on the aggregation return, and insert a 0 if it's an empty list... – OJT Apr 27 '21 at 14:41
  • can you show updated post. if it does not resolve then can you please add some document in [playground](https://mongoplayground.net/) and share link. – turivishal Apr 27 '21 at 14:44