'MongoDB find documents with a field not empty, IF EXISTS

I want to be able to query documents having a specific field's value not empty, if the field is present. Normally I'd do something like:

client_comment: { '$exists': true, '$ne': null },
'$expr': { '$eq': [ { "$ne": "$client_comment" }, null ] },

The thing is, some of the records have this field, some of them don't... and some of them are empty strings. I want to exclude those who have client_comment value as empty strings but I don't know how do I do that.

{
    "_id" : ObjectId("5e0b1b094dfee4f3a2f4f62a"),
    "name" : "Jane Doe",
    "email" : "[email protected]",
    "status" : "done",
    "created_date" : ISODate("2021-10-03T19:38:56.462Z"),
    "updated_date" : ISODate("2021-10-03T19:38:56.462Z"),
}
   
{
    "_id" : ObjectId("5e0b1b094dcee4f3a2f4f62a"),
    "name" : "Lorem Ipsum",
    "email" : "[email protected]",
    "status" : "done",
    "created_date" : ISODate("2021-10-03T19:38:56.462Z"),
    "updated_date" : ISODate("2021-10-03T19:38:56.462Z"),
    "client_comment" : "Lorem ipsum",
}

// Exclude this record from the result
{
    "_id" : ObjectId("5e0b1b094dfef4f3a2f4f62a"),
    "name" : "John Doe",
    "email" : "[email protected]",
    "status" : "done",
    "created_date" : ISODate("2021-10-03T19:38:56.462Z"),
    "updated_date" : ISODate("2021-10-03T19:38:56.462Z"),
    "client_comment" : "",
}
mongodbtypeormrobo3t


Solution 1:[1]

Query1

  • find solution
  • keep document if field is missing or its not empty string

Playmongo

find({"$or": 
 [{"client_comment": {"$exists": false}}, {"client_comment": {"$ne": ""}}]})

Query2

  • aggregate solution
  • keep document if field is missing or its not empty string

Playmongo

aggregate(
[{"$match": 
    {"$expr": 
      {"$or": 
        [{"$eq": [{"$type": "$client_comment"}, "missing"]},
         {"$ne": ["$client_comment", ""]}]}}}])

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions