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.

Added From comment section:

To check if the value doesn't exist or is null use { $lte: ["$field", null] }

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 :)

In short

{'$project': {
    'field_exists': {'$or': [
        {'$eq': ['$field', null]}, 
        {'$gt': ['$field', null]},
    ]},
}}

---

Details

$exists means that the field exists, even if it is null or any other empty value. That is why all the answers on this page are incorrect.

Let's test a bit. Check this:

// Let's take any collection that have docs
db.getCollection('collection').aggregate([
  // Get arbitrary doc, no matter which, we won't use it
  {"$limit": 1},
  // Project our own fields (just create them with $literal)
  {'$project': {
    '_id': 0,
    'null_field': {'$literal': null},
    'not_null_field': {'$literal': {}},
  }},
])

We'll get this:

{
    "null_field" : null,
    "not_null_field" : {}
}

Then let's clarify which fields exist in this doc:

1. null_field - exists
2. not_null_field - exists
3. non_existent_field - doesn't.

Okay, it's time to test project stage I've mentioned above. Let's add it for every field we're interested in:

{'$project': {
    'null_field_exists': {'$or': [
        {'$eq': ['$null_field', null]}, 
        {'$gt': ['$null_field', null]},
    ]},
    'not_null_field_exists': {'$or': [
        {'$eq': ['$not_null_field', null]}, 
        {'$gt': ['$not_null_field', null]},
    ]},
    'non_existent_field_exists': {'$or': [
        {'$eq': ['$non_existent_field', null]}, 
        {'$gt': ['$non_existent_field', null]},
    ]},
}},

What we get is:

{
    "null_field_exists" : true,
    "not_null_field_exists" : true,
    "non_existent_field_exists" : false
}

Correct!

And a small note: we use null for comparing because it is the smallest value at least valuable (smaller is just the non-existence).

In mongoose only following working

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

I solved it by using $addFields with $ifNull, and then $match the added field by checking if its value is null.

collection.aggregate(
    [
       {
          $addFields:{
              fieldName:{
                 $ifNull:["$fieldToCheckIfExists", null]
              }
          }
       },
       {
          $match:{
              fieldName:{
                $ne: null
          }
       }
    ]

A semantically transparent solution to check if a field exists and is not null:

{ $ne: [{ $ifNull: ["$field", null] }, null] }

To check if it is missing, replace $ne with $eq.

My answer is:

{$match:{
    $and:[{
        name:{
            $exists:true
        }
    }, {
        $expr:{
            $eq:["$$id", "$_id"]
        }
    }]
}}

I use this in lookup, on my pipeline stage. This post 2 rules the first one, name must exist. And the second thing is the relation between these 2 collection. I Am sure u can modify this for your question.

In Group, if you want to count the existing fields, but not group as the other answers shows, you can use

{
  _id: "$groupField",
  qtyExists: {
    $sum: {
      $cond: ["$field", 1, 0]
    }
  },
  qtyNotExists: {
    $sum: {
      $cond: ["$field", 0, 1]
    }
  },
}