CODEWITHSUNDEEP
mongodbdatabasenosql
TIMEX
TIMEX

Reputation: 271764

How do you query for "is not null" in Mongo?

I would like to execute a following query:

db.mycollection.find(HAS IMAGE URL)

What should be the correct syntax?

Upvotes: 701

Views: 946131

Answers (12)

ray
ray

Reputation: 15227

In SQL, you will often use the canonical way of COALESCE to fallback NULL into empty string "" and check if the final result is equal to "" to catch both NULL and empty string cases.

In MongoDB, $ifNull achieve similar functionality that can handle below cases:

  1. the field has a value null
  2. the field does not exists

So, you can use it to fallback to empty string to catch the empty string case.

db.collection.find({
  "$expr": {
    $ne: [
      {
        $ifNull: [
          "$field",
          ""
        ]
      },
      ""
    ]
  }
})

Mongo Playground

Upvotes: 0

Aanchal Mehrotra
Aanchal Mehrotra

Reputation: 99

The following query will check if field exists, not null and not empty string

db.collection.find({
  $and: [
    { fieldName: { $exists: true } },
    { fieldName: { $ne: null } },
    { fieldName: { $ne: "" } }
  ]
})

Upvotes: 1

SahilTyagi
SahilTyagi

Reputation: 45

You can use $and and $exists with $nin for finding the scenario for not null value for the key.

use dbname db.collectionname.find({$and : [{key : {$exists : true}},{$nin : [null,'',undefined]}]});

Since a value can either be present or not present. For that case $exists will work to check the value. And for the case where value exists to check the details of that, $nin is used to exclude undefined, null and empty string.

Upvotes: 3

user2293072
user2293072

Reputation: 786

In pymongo you can use:

db.mycollection.find({"IMAGE URL":{"$ne":None}});

Because pymongo represents mongo null as python None.

Upvotes: 61

Farouk Elkholy
Farouk Elkholy

Reputation: 943

db.collection_name.find({"filed_name":{$exists:true}});

fetch documents that contain this filed_name even it is null.

Warning

db.collection_name.find({"filed_name":{$ne:null}});

fetch documents that its field_name has a value $ne to null but this value could be an empty string also.

My proposition:

db.collection_name.find({ "field_name":{$ne:null},$where:"this.field_name.length >0"})

Upvotes: 69

Tim Gautier
Tim Gautier

Reputation: 30112

This will return all documents with a key called "IMAGE URL", but they may still have a null value.

db.mycollection.find({"IMAGE URL":{$exists:true}});

This will return all documents with both a key called "IMAGE URL" and a non-null value.

db.mycollection.find({"IMAGE URL":{$ne:null}});

Also, according to the docs, $exists currently can't use an index, but $ne can.

Edit: Adding some examples due to interest in this answer

Given these inserts:

db.test.insert({"num":1, "check":"check value"});
db.test.insert({"num":2, "check":null});
db.test.insert({"num":3});

This will return all three documents:

db.test.find();

This will return the first and second documents only:

db.test.find({"check":{$exists:true}});

This will return the first document only:

db.test.find({"check":{$ne:null}});

This will return the second and third documents only:

db.test.find({"check":null})

Upvotes: 1292

Ankit Marothi
Ankit Marothi

Reputation: 1005

In an ideal case, you would like to test for all three values, null, "" or empty(field doesn't exist in the record)

You can do the following. 

db.users.find({$and: [{"name" : {$nin: ["", null]}}, {"name" : {$exists: true}}]})

Upvotes: 12

Taha Hamedani
Taha Hamedani

Reputation: 113

The simplest way to check the existence of the column in mongo compass is :

{ 'column_name': { $exists: true } }

Upvotes: 3

Amitesh Bharti
Amitesh Bharti

Reputation: 15713

One liner is the best :

db.mycollection.find({ 'fieldname' : { $exists: true, $ne: null } });

Here,

mycollection : place your desired collection name

fieldname : place your desired field name

Explaination :

$exists : When is true, $exists matches the documents that contain the field, including documents where the field value is null. If is false, the query returns only the documents that do not contain the field.

$ne selects the documents where the value of the field is not equal to the specified value. This includes documents that do not contain the field.

So in your provided case following query going to return all the documents with imageurl field exists and having not null value:

db.mycollection.find({ 'imageurl' : { $exists: true, $ne: null } });

Upvotes: 163

Berhanu Tarekegn
Berhanu Tarekegn

Reputation: 328

db.<collectionName>.find({"IMAGE URL":{"$exists":"true"}, "IMAGE URL": {$ne: null}})

Upvotes: 3

Xameer
Xameer

Reputation: 31237

Sharing for future readers.

This query worked for us (query executed from MongoDB compass):

{
  "fieldName": {
    "$nin": [
      "",
      null
    ]
  }
}

Upvotes: 24

Adam Comerford
Adam Comerford

Reputation: 21682

An alternative that has not been mentioned, but that may be a more efficient option for some (won't work with NULL entries) is to use a sparse index (entries in the index only exist when there is something in the field). Here is a sample data set:

db.foo.find()
{ "_id" : ObjectId("544540b31b5cf91c4893eb94"), "imageUrl" : "http://example.com/foo.jpg" }
{ "_id" : ObjectId("544540ba1b5cf91c4893eb95"), "imageUrl" : "http://example.com/bar.jpg" }
{ "_id" : ObjectId("544540c51b5cf91c4893eb96"), "imageUrl" : "http://example.com/foo.png" }
{ "_id" : ObjectId("544540c91b5cf91c4893eb97"), "imageUrl" : "http://example.com/bar.png" }
{ "_id" : ObjectId("544540ed1b5cf91c4893eb98"), "otherField" : 1 }
{ "_id" : ObjectId("544540f11b5cf91c4893eb99"), "otherField" : 2 }

Now, create the sparse index on imageUrl field:

db.foo.ensureIndex( { "imageUrl": 1 }, { sparse: true } )
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 1,
    "numIndexesAfter" : 2,
    "ok" : 1
}

Now, there is always a chance (and in particular with a small data set like my sample) that rather than using an index, MongoDB will use a table scan, even for a potential covered index query. As it turns out that gives me an easy way to illustrate the difference here:

db.foo.find({}, {_id : 0, imageUrl : 1})
{ "imageUrl" : "http://example.com/foo.jpg" }
{ "imageUrl" : "http://example.com/bar.jpg" }
{ "imageUrl" : "http://example.com/foo.png" }
{ "imageUrl" : "http://example.com/bar.png" }
{  }
{  }

OK, so the extra documents with no imageUrl are being returned, just empty, not what we wanted. Just to confirm why, do an explain:

db.foo.find({}, {_id : 0, imageUrl : 1}).explain()
{
    "cursor" : "BasicCursor",
    "isMultiKey" : false,
    "n" : 6,
    "nscannedObjects" : 6,
    "nscanned" : 6,
    "nscannedObjectsAllPlans" : 6,
    "nscannedAllPlans" : 6,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "server" : "localhost:31100",
    "filterSet" : false
}

So, yes, a BasicCursor equals a table scan, it did not use the index. Let's force the query to use our sparse index with a hint():

db.foo.find({}, {_id : 0, imageUrl : 1}).hint({imageUrl : 1})
{ "imageUrl" : "http://example.com/bar.jpg" }
{ "imageUrl" : "http://example.com/bar.png" }
{ "imageUrl" : "http://example.com/foo.jpg" }
{ "imageUrl" : "http://example.com/foo.png" }

And there is the result we were looking for - only documents with the field populated are returned. This also only uses the index (i.e. it is a covered index query), so only the index needs to be in memory to return the results.

This is a specialized use case and can't be used generally (see other answers for those options). In particular it should be noted that as things stand you cannot use count() in this way (for my example it will return 6 not 4), so please only use when appropriate.

Upvotes: 3

Related Questions