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?

Solution 1

Use $ne (for "not equal")

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

Solution 2

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

Solution 3

i find that this works for me

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

Solution 4

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

Solution 5

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.

Solution 6

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

Solution 7

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