Testing field existence in aggregation

Working with Data
1

My goal is to compute the sum of conditional fields. Let’s say I have a collection of documents containing always a and b fields, and sometimes c. I want to aggregate the values of c when it exists, or b when c does not exist.

Here is a snippet:

db.create_collection("test")
coll = db["test"]
coll.insert_many([{"a":5, "b":8}, {"a":3, "b":3, "c":4}, {"a":5, "b":1}, {"a":2, "b":7, "c":9}])
result = coll.aggregate([
                         {"$addFields": {"d": {"$cond": [{"$eq": ["$c", None]}, "$b", "$c"]}}},
                         {"$group": {
                           "_id": None,
                           "total": {"$sum": "$d"},
                           "count": {"$sum": 1}
                         }}
                        ])
next(cursor)

But this does not work, the result is 13 instead of 22. It seems that only c values have been summed, i.e. the $eq condition always returned false. How do you test that a field does not exist in an aggregation? I’ve read in the documentation (Query for Null or Missing Fields — MongoDB Manual) that testing the equality with None could mean that the field was affected to None or that the field did not exist. But the given examples only concerned search, not aggregation. Why is the behavior different?

2

Hello,

There is a way to check for field existence by using $ifNull expression explained here
It evaluates input expressions for null values, assigns a value if it’s null, in this case I assigned the value of “b” to be the value if “c” is null.

I tried your example in my testing environment and it produced the expected output of 22, please check the example below:

MongoDB Enterprise M040:PRIMARY> db.test.insertMany([{"a":5, "b":8}, {"a":3, "b":3, "c":4}, {"a":5, "b":1}, {"a":2, "b":7, "c":9}])
{
	"acknowledged" : true,
	"insertedIds" : [
		ObjectId("6352b73d645b4616bd7626b2"),
		ObjectId("6352b73d645b4616bd7626b3"),
		ObjectId("6352b73d645b4616bd7626b4"),
		ObjectId("6352b73d645b4616bd7626b5")
	]
}
MongoDB Enterprise M040:PRIMARY> 
MongoDB Enterprise M040:PRIMARY> db.test.aggregate([
...                          {"$addFields": {"d": { $ifNull: [ "$c", "$b" ] }}},
...                          {"$group": {
...                            "_id": null,
...                            "total": {"$sum": "$d"},
...                            "count": {"$sum": 1}
...                          }}
...                         ])
{ "_id" : null, "total" : 22, "count" : 4 }

I hope you find this helpful.

3

Hello,

Yes, I know $ifNull but in my case, I need to check the existence of another field (let’s say c) in order to sum on a or b. That’s why I’d like to keep the $cond structure (or equivalent). The example has been simplified to make it small and simple.

I think that $eq should behave the same in searches or aggregations.