CODEWITHSUNDEEP
mongodb
AlbertEngelB
AlbertEngelB

Reputation: 16456

Check that Field Exists with MongoDB

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?

Upvotes: 216

Views: 264172

Answers (8)

Code Guru
Code Guru

Reputation: 15598

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 } });

Upvotes: 1

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230521

Use $ne (for "not equal")

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

Upvotes: 323

Rafiq
Rafiq

Reputation: 11535

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
      }
    }
  }
])

Upvotes: 0

arpit1714
arpit1714

Reputation: 569

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'}})

Upvotes: 0

Dror
Dror

Reputation: 5505

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.

Upvotes: 4

Hardik Gajjar
Hardik Gajjar

Reputation: 1024

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

Upvotes: 2

Pavan Choudhary
Pavan Choudhary

Reputation: 469

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

Upvotes: 46

Yakir Manor
Yakir Manor

Reputation: 4839

i find that this works for me

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

Upvotes: 6

Related Questions