63

Here is my data structure. 

[{
"name": "David",
"lastname": "",
},
{
"name": "Angela"
}]

"lastname" is sometimes present and sometimes not and sometime is "".

I want to get all rows that have lastname not equal to "". But this does not work. It returns both the rows when lastname is "" and when lastname is not present at all. in the example above I want to only get the David node.

db.collection.find( {"lastname": {"$ne": ""}} )
David Dehghan
  • 22,159
  • 10
  • 107
  • 95

4 Answers4

143
db.collection.find({"lastname" : {"$exists" : true, "$ne" : ""}})

In the mongo shell (id's omitted to save space)

> db.collection.find()
  { "name" : "Angela" }
  { "name" : "David", "lastname" : "" }
  { "name" : "Kyle",  "lastname" : "Test" }
  { "name" : "John",  "lastname" : null }

> db.collection.find({"lastname" : {"$exists" : true, "$ne" : ""}})
  { "name" : "Kyle", "lastname" : "Test" }
  { "name" : "John",  "lastname" : null }

In case you also want to filter out matches against null values you need to adjust the criteria as follows (we can also get rid of $exists as "$ne": null takes care of this)

> db.collection.find({$and:[{"lastname": {"$ne": null}}, {"lastname": {"$ne": ""}}]})
  { "name" : "Kyle", "lastname" : "Test" }
kasur
  • 1,542
  • 15
  • 15
Kyle
  • 21,978
  • 2
  • 60
  • 61
  • 1
    the quotes around $ne are needed for python. so the expression {$ne: ""} evaluates to true both when level2_c is "" and when level2_c node does not exists to begin with. So i had to filter them out like this {"level2_c": {"$exists": True}, "level1_b.level2_c": {"$ne": ""} }. this works but looks somewhat ugly. – David Dehghan Mar 14 '12 at 01:27
  • 2
    I was testing from the mongo shell. Glad you found the correct answer. – Kyle Mar 14 '12 at 01:30
  • Thanks. I clarified the question a bit. can you please look at it again. is there a better answer than this: {"lastname": {"$exists": True}, "lastname": {"$ne": ""} } ? – David Dehghan Mar 14 '12 at 01:42
  • @DavidDehghan I edited the answer. I think this is the best you're gonna get. One note, your question says you want the david record back but you also said you only want records with a lastname defined AND a value not equal to "". Am I missing something? – Kyle Mar 14 '12 at 01:59
  • 10
    I believe you can simplify the `null` check to something like: `db.collection.find({"lastname": {$nin: ["", null]}})` – dule Feb 09 '15 at 16:29
  • If you wonder why $and needs to be used explicitly in the last example, this is the reason: https://dba.stackexchange.com/a/156258/155283 ($ne cannot be used twice in same block) – cprcrack Jul 14 '18 at 08:06
15

Facing this problem I thought in another solution:

db.collection.find({"lastname": {"$gte": " "}})

With this I could get only the not empty strings, also ignoring null and not existent field, because any printable value (ASCII) has a greater value than space (32).

https://en.wikipedia.org/wiki/ASCII

Joao Roberto joaogameprog
  • 151
  • 1
  • 4
2

You can use a regex query:

db.test.find({ "lastname": /(.|\s)*\S(.|\s)*/ })

This regex matches strings beginning or ending with 0 or N whitespaces (.|\s) and it have to be one or more non-whitespaces \S in the middle.

Marcos Godinho
  • 84
  • 3
  • I don't understand the periods, though. Why do you have `(.|\s)` if you are looking just for spaces (`\s`)? – Brandt Feb 27 '19 at 18:45
0

I'm not sure if this helps, but it has worked for me. The regex .+ returns anything that contains more than 1 character, whereas .* returns 0 or more. So it won't return anything less than 0 characters.

In terms of strings containing only whitespace, I don't think this solution can handle that.

Jadboy20
  • 3
  • 1
  • 1
  • 2