Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find documents where value is not null

Tags:

mongodb

I have the following doc structure

{
    "model" : {

    },
    "_id" : ObjectId("538df963d1c3f82329000257"),
    "email" : "[email protected]",
    "name" : "sam",
    "endpointarn" : "arn:aws:sns:us-east-1:284585229836:endpoint/GCM/Positivethinking/59eb7c66-c13c-3e44-af80-0b1f4f7ea9fd",
    "devregidtoken" : "APA91bFMOBo6ZWemMAG5clKEd5VYVvbeDOM5zNXjbbl3faSE0FZt3gtJYv0hpjdWuOY-mvr-k3sCcq0dEveCM6jx5mOh1u6JEubuUmKb2zU64dn_A4gJ4pCBG7AGQJ8DnkO83Ca4wgzsoJcio9T-TtA",
    "topicsubs" : [
        {
            "topicsubid" : "arn:aws:sns:us-east-1:284585229836:Wealth-ProsperityQuotes:5f3e8060-48fa-4a8e-bdc3-e9596747da1a",
            "topicname" : "Wealth-Prosperity",
            "topicno" : 0
        }
    ],
    "timecreated" : ISODate("2014-06-03T16:35:47.442Z"),
    "purchasedata" : {
        "orderid" : "111",
        "packagename" : "",
        "productid" : "",
        "purchasetime" : "",
        "purchasestate" : "",
        "developerpayload" : "",
        "purchasetoken" : ""
    },
    "ccode" : "",
    "ccodestat" : ""
}

I want to get all documents where purchasedata.orderid IS NOT NULL. I have tried

db.User.find({"purchasedata.orderid" : {$ne : ""}});
like image 959
Jase Whatson Avatar asked Jun 06 '14 10:06

Jase Whatson


People also ask

How do I search for NOT NULL in MongoDB?

To query for is not null value, we can use the $ne operator as well as the $eq operator and specify the desired value that we want to query for. This guide aims to provide readers with different ways and examples for the same to query for is not null values in MongoDB.

How do I filter non null in MongoDB?

This MongoDB article will explain how you can query non-null values in MongoDB. To query for the is not null value, you can use the $ne operator and the $eq operator, then specify the desired value that you want to query for.

How do I query NULL values in MongoDB?

MongoDB fetch documents containing 'null' If we want to fetch documents from the collection "testtable" which contains the value of "interest" is null, the following mongodb command can be used : >db. testtable. find( { "interest" : null } ).


3 Answers

If you're checking for null you can use $ne operator:

db.User.find({
   "purchasedata.orderid" : { $ne : null }
});

Note:

OP originally wanted to check that the value is not an empty string "" and not null. null and empty string are two different BSON types. You can use $type and $not operators to check where the value is null and that the key exists:

db.User.find({
   "purchasedata.orderid" : { 
      $not : { $type : 10 }, 
      $exists : true
   }
});

The $type operator selects documents where the value is a specific BSON type (10 corresponds to Null). $exists will check that the key exists in the subdocument.

like image 115
Christian P Avatar answered Oct 09 '22 01:10

Christian P


You were very close. If the value is actually null, you can just use "null" instead of blank.

db.User.find({"purchasedata.orderid" : {$ne : null}});
like image 44
vintastic Avatar answered Oct 09 '22 01:10

vintastic


So this is what finally worked for me

db.User.find({
   "purchasedata.orderid" : {
      $exists : true,
      $ne : ""
   }
});

@Christian p was close so I up voted, but I think my problem was the value wasn't null but actually a empty string, also checking if the value exists is important

like image 16
Jase Whatson Avatar answered Oct 09 '22 02:10

Jase Whatson