How do you query for is not null in Mongo

Question

I would like to execute a following query   db mycollection find HAS IMAGE URL    What should be the correct syntax

User · Answer

the Query Will be   db mycollection find   IMAGE URL     exists   true       it will return all documents having  IMAGE URL  as a key

User · Answer

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

User · Answer

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

User · Answer

The simplest way to check the existence of the column in mongo compass is       column name      exists  true

User · Answer

In pymongo you can use   db mycollection find   IMAGE URL     ne  None       Because pymongo represents mongo  null  as python  None

User · Answer

db  lt collectionName gt  find   IMAGE URL     exists   true     IMAGE URL     ne  null

User · Answer

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

User · Answer

This will return all documents with a key called  quot IMAGE URL quot   but they may still have a null value  db mycollection find   quot IMAGE URL quot    exists true      This will return all documents with both a key called  quot IMAGE URL quot  and a non-null value  db mycollection find   quot IMAGE URL quot    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   quot num quot  1   quot check quot   quot check value quot     db test insert   quot num quot  2   quot check quot  null    db test insert   quot num quot  3     This will return all three documents  db test find     This will return the first and second documents only  db test find   quot check quot    exists true      This will return the first document only  db test find   quot check quot    ne null      This will return the second and third documents only  db test find   quot check quot  null

User · Answer

Thanks for providing a solution  I noticed in MQL  sometimes  ne null doesn t work instead we need to use syntax  ne  quot  quot  i e  in the context of above example we would need to use db mycollection find   quot IMAGE URL quot    quot  ne quot   quot  quot     - Not sure why this occurs  I have posted this question in the MongoDB forum  following is the snapshot showing example  enter image description here

User · Answer

db collection name find   quot filed name quot    exists true      fetch documents that contain this filed name even it is null  Warning db collection name find   quot filed name quot    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    quot field name quot    ne null   where  quot this field name length  gt 0 quot

User · Answer

Sharing for future readers    This query worked for us  query executed from MongoDB compass         fieldName           nin                     null

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

Examples related to mongodb

Examples related to database

Examples related to nosql