mongodbtypeormrobo3t

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" : "john_doe@gmail.com",
    "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" : "test@gmail.com",
    "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" : "jane.doe@gmail.com",
    "status" : "done",
    "created_date" : ISODate("2021-10-03T19:38:56.462Z"),
    "updated_date" : ISODate("2021-10-03T19:38:56.462Z"),
    "client_comment" : "",
}

Solution

  • Query1

    Playmongo

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

    Query2

    Playmongo

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