mongodbstudio3t

How to exclude a field that contains text, and return both null and does not exist?


I'm using Studio3T to run MongoDB, but I'm a beginner. I have a publication.notes field that I use to exclude items from a report, this field is normally not used otherwise so a majority of the documents do NOT have this field populated and we have been running this code below for the report. (publication.notes is the last line)

{
$and: [
    {
        "warehouses.code": "RSDMAIN" 
    }
    ,
    {
        "warehouses.0.quantity": {
            $gt: NumberInt(0) 
        }
    }
    ,
    {
        "images.0.image": {
            $exists: false 
        }
    }
    ,
    {
        "featured_image.slug": {
            $exists: false 
        }
    }
    ,
    {
        "publication.status": {
            $ne: "discontinued" 
        }
    }
    ,
    {
        "publication.status": {
            $ne: "rejected" 
        }
    }
    ,
    {
        "product_type": {
            "$in":[
                "standard" ,
                null 
            ]
        }
    }
    ,
    {
        "publication_notes": {
            $exists: false 
        }
    }
]
}

The problem is that we have had a small percentage of documents that the publication notes does get populated, but it stays null. I would like my report to contain both the null and the does not exist documents, while still excluding the ones with actual text. Everything I try makes me either null OR DNE.

I tried playing around with "publication.notes": { $not: { $type: 2 } } , but they return the same report as "publication_notes": { $exists: false }, which makes me think that it is seeing whatever that is in the field as a string, even if it is null.


Solution

  • Please try publication_notes : null to get both null and not exists

    sample collection

    > db.t44.find()
    { "_id" : ObjectId("5c3543fc01f1171d3864b924"), "a" : "asdaw" }
    { "_id" : ObjectId("5c35440201f1171d3864b925"), "a" : null }
    { "_id" : ObjectId("5c35440601f1171d3864b926") }
    

    find result

    > db.t44.find({a : null})
    { "_id" : ObjectId("5c35440201f1171d3864b925"), "a" : null }
    { "_id" : ObjectId("5c35440601f1171d3864b926") }
    >