Test empty string in mongodb and pymongo

Question

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

kasur · Accepted Answer · 2014-10-17 17:22:15Z

153

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

edited Oct 17, 2014 at 17:22

kasur

1,60016 silver badges15 bronze badges

answered Mar 14, 2012 at 0:43

Kyle

22.3k2 gold badges63 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

David Dehghan Over a year ago

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.

Kyle Over a year ago

I was testing from the mongo shell. Glad you found the correct answer.

David Dehghan Over a year ago

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

Kyle Over a year ago

@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?

dule Over a year ago

I believe you can simplify the null check to something like: db.collection.find({"lastname": {$nin: ["", null]}})

|

Joao Roberto joaogameprog · Accepted Answer · 2020-07-29 00:31:17Z

18

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

answered Jul 29, 2020 at 0:31

Joao Roberto joaogameprog

1811 silver badge4 bronze badges

2 Comments

Michael Kubler Over a year ago

I was looking for an easy solution and this is exactly what I wanted. It's elegant and works well. Thank you.

mons droid Over a year ago

smooth smooth .. brain here. noice!

Marcos Godinho · Accepted Answer · 2018-01-05 18:42:23Z

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.

answered Jan 5, 2018 at 18:42

Marcos Godinho

1141 silver badge3 bronze badges

1 Comment

Brandt Over a year ago

I don't understand the periods, though. Why do you have (.|\s) if you are looking just for spaces (\s)?

Jadboy20 · Accepted Answer · 2021-03-08 23:10:37Z

1

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.

answered Mar 8, 2021 at 23:10

Jadboy20

231 gold badge1 silver badge5 bronze badges

Collectives™ on Stack Overflow

Test empty string in mongodb and pymongo

4 Answers 4

6 Comments

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related