MongoDB aggregate by field exists

I have a hard time believing this question hasn't been asked and answered somewhere already, but I can't find any trace of it.

I have a MongoDB aggregation query that needs to group by a boolean: the existence of another field.

For example let's start with this collection:

> db.test.find()
{ "_id" : ObjectId("53fbede62827b89e4f86c12e"),
  "field" : ObjectId("53fbede62827b89e4f86c12d"), "name" : "Erik" }
{ "_id" : ObjectId("53fbee002827b89e4f86c12f"), "name" : "Erik" }
{ "_id" : ObjectId("53fbee092827b89e4f86c131"),
  "field" : ObjectId("53fbee092827b89e4f86c130"), "name" : "John" }
{ "_id" : ObjectId("53fbee122827b89e4f86c132"), "name" : "Ben" }

2 documents have "field", and 2 don't. Note that each value of "field" may be different; we just want to group on its existence (or non-nullness works for me too, I don't have any null values stored).

I've tried using $project, but $exists doesn't exist there, and $cond and $ifNull haven't helped me. The field always appears to exist, even when it doesn't:

> db.test.aggregate(
  {$project:{fieldExists:{$cond:[{$eq:["$field", null]}, false, true]}}},
  {$group:{_id:"$fieldExists", count:{$sum:1}}}
)
{ "_id" : true, "count" : 4 }

I would expect the following much simpler aggregate to work, but for some reason $exists isn't supported in this way:

> db.test.aggregate({$group:{_id:{$exists:"$field"}, count:{$sum:1}}})
assert: command failed: {
  "errmsg" : "exception: invalid operator '$exists'",
  "code" : 15999,
  "ok" : 0
} : aggregate failed
Error: command failed: {
  "errmsg" : "exception: invalid operator '$exists'",
  "code" : 15999,
  "ok" : 0
} : aggregate failed
    at Error (<anonymous>)
    at doassert (src/mongo/shell/assert.js:11:14)
    at Function.assert.commandWorked (src/mongo/shell/assert.js:244:5)
    at DBCollection.aggregate (src/mongo/shell/collection.js:1149:12)
    at (shell):1:9
2014-08-25T19:19:42.344-0700 Error: command failed: {
  "errmsg" : "exception: invalid operator '$exists'",
  "code" : 15999,
  "ok" : 0
} : aggregate failed at src/mongo/shell/assert.js:13

Does anyone know how to get the desired result from a collection like this?

Expected result:

{ "_id" : true, "count" : 2 }
{ "_id" : false, "count" : 2 }

I solved the same problem just last night, this way:

> db.test.aggregate({$group:{_id:{$gt:["$field", null]}, count:{$sum:1}}})
{ "_id" : true, "count" : 2 }
{ "_id" : false, "count" : 2 }

See http://docs.mongodb.org/manual/reference/bson-types/#bson-types-comparison-order for a full explanation of how this works.

I solved it with checking for undefined

$ne : [$var_to_check, undefined]

or

$ne:  [ { $type : "$var_to_check"}, 'missing'] }

This returns true if the var is defined

The $exists operator is a "query" operator, so it is used basically to "filter" results rather than identify a logical condition.

As a "logical" operator the aggregation framework supports the $ifNull operator. This returns the field value where it exists or the alternate supplied value where it does not or otherwise evaluates to null

db.test.aggregate([
    { "$group": {
        "_id": { "$ifNull": [ "$field", false ] },
        "count": { "$sum": 1 }
    }}
])

But of course, even that is not a "true/false" comparison, so unless you actually want to return the actual value of the field where it is present, then you are probably better off with a $cond statement much like you have:

db.test.aggregate([
    { "$group": {
        "_id": { "$cond": [{ "$eq": [ "$field", null ] }, true, false ] },
        "count": { "$sum": 1 }
    }}
])

Where $ifNull can be very useful is in replacing not existent array fields that would otherwise cause an error using $unwind. You can then do something like return a single element or empty array so this does not cause problems in the rest of your pipeline processing.

Dunno how it was but now in 2019 there is clean solution. In aggregation pipeline do this

$match: {"my_field": {$ne: null}}

Nice thing is in my lang 'ne' means not :)