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" : "",
}

CodePudding user response：

Query1

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

Test code here

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

Query2

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

Test code here

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