Check that Field Exists with MongoDB

Question

So I'm attempting to find all records who have a field set and isn't null.

I try using $exists, however according to the MongoDB documentation, this query will return fields who equal null.

$exists does match documents that contain the field that stores the null value.

So I'm now assuming I'll have to do something like this:

db.collection.find({ "fieldToCheck" : { $exists : true, $not : null } })

Whenever I try this however, I get the error [invalid use of $not] Anyone have an idea of how to query for this?

Sergio Tulentsev · Accepted Answer · 2022-07-04T08:08:57.107

305

Use $ne (for "not equal")

db.collection.find({ "fieldToCheck": { $ne: null } })

edited Jul 04 '22 at 08:08

answered Nov 08 '13 at 20:34

Sergio Tulentsev

226,338
43
373
367

1

What does this return? A null collection? A single item? An array? – Oliver Dixon Dec 17 '15 at 19:37
10

@iLoveUnicorns: what `find` always returns: a collection of records, matching the criteria. – Sergio Tulentsev Dec 17 '15 at 19:38
4

@SergioTulentsev AFAIK it returns a cursor – fernandohur Feb 02 '16 at 20:18
@fernandohur: yeah, but if you have less than one page of documents, you won't even see the difference. And if you were to run this query from external driver, I'm pretty sure most of them shield you from the cursor implementation detail. – Sergio Tulentsev Feb 03 '16 at 06:36
1

I know this is old, but as this is the accepted answer: `{$exists: true}` is redundant, just `{$ne: null}` is enough. – Sergei Kuzmin Jul 03 '22 at 01:12
@SergeiKuzmin: thanks for pointing this out. It is enough now, but (I'm pretty sure) this wasn't the case back then. – Sergio Tulentsev Jul 04 '22 at 08:08

score 45 · Answer 2 · edited May 12 '20 at 14:15

45

Suppose we have a collection like below:

{ 
  "_id":"1234"
  "open":"Yes"
  "things":{
             "paper":1234
             "bottle":"Available"
             "bottle_count":40
            } 
}

We want to know if the bottle field is present or not?

Ans:

db.products.find({"things.bottle":{"$exists":true}})

edited May 12 '20 at 14:15

deadshot

8,881
4
20
39

answered Aug 16 '15 at 14:41

Pavan Choudhary

459
4
2

4

`When is true, $exists matches the documents that contain the field, including documents where the field value is null.` [From the docs.](http://docs.mongodb.org/manual/reference/operator/query/exists/) – AlbertEngelB Aug 16 '15 at 15:14
1

yep, but i don' see why a DB would contain the value null, it's sloppy – Martijn Scheffer Aug 13 '16 at 17:56

score 6 · Answer 3 · answered Apr 02 '17 at 14:12

6

i find that this works for me

db.getCollection('collectionName').findOne({"fieldName" : {$ne: null}})

answered Apr 02 '17 at 14:12

Yakir Manor

4,687
1
32
25

score 3 · Answer 4 · answered Sep 24 '21 at 12:08

This comment is written in 2021 and applies for MongoDB 5.X and earlier versions.

If you value query performance never use $exists (or use it only when you have a sparse index over the field that is queried. the sparse index should match the criteria of the query, meaning, if searching for $exists:true, the sparse index should be over field:{$exist:true} , if you are querying where $exists:true the sparse index should be over field:{$exist:false}

Instead use :

db.collection.find({ "fieldToCheck": {  $ne: null } })

or

db.collection.find({ "fieldToCheck": {  $eq: null } })

this will require that you include the fieldToCheck in every document of the collection, however - the performance will be vastly improved.

See my answer: https://stackoverflow.com/a/72843232/1301837. It explains the current limitations of `$exists` with full index and the reason. — Sergei Kuzmin, Jul 03 '22 at 00:24

score 2 · Answer 5 · answered Aug 09 '20 at 15:21

2

db.<COLLECTION NAME>.find({ "<FIELD NAME>": { $exists: true, $ne: null } })

answered Aug 09 '20 at 15:21

Hardik Gajjar

1,024
12
27

score 1 · Answer 6 · answered Oct 27 '22 at 11:21

In my case, i added new field isDeleted : true to only fields that are deleted.

So for all other records there was no isDeleted field, so i wanted to get all the fields that isDeleted either does not exist or false. So query is

.find({ isDeleted: { $ne: true } });

score 0 · Answer 7 · answered Oct 18 '21 at 07:03

I Tried to convert it into boolean condition , where if document with table name already exist , then it will append in the same document , otherwise it will create one .

table_name is the variable using which i am trying to find the document

query = { table_name : {"$exists": "True"}}
    
    result = collection.find(query)
    flag = 0
    for doc in result:
        collection.update_one({}, { "$push" : { table_name : {'name':'hello'} } } )
        flag = 1
    if (flag == 0):
        collection.insert_one({ table_name : {'roll no' : '20'}})

score 0 · Answer 8 · answered Feb 11 '22 at 16:59

aggregate example

https://mongoplayground.net/p/edbKil4Zvwc

db.collection.aggregate([
  {
    "$match": {
      "finishedAt": {
        "$exists": true
      }
    }
  },
  {
    "$unwind": "$tags"
  },
  {
    "$match": {
      "$or": [
        {
          "tags.name": "Singapore"
        },
        {
          "tags.name": "ABC"
        }
      ]
    }
  },
  {
    "$group": {
      "_id": null,
      "count": {
        "$sum": 1
      }
    }
  }
])

Check that Field Exists with MongoDB

8 Answers8

Linked

Related