MongoDB queries with null value

Question

My collection (MongoDB v 2.0.2) has following records:

db.organization.find({})
{ "_id" : 1001, "path" : [ ], "parent" : null }
{ "_id" : 1002, "path" : [ 1001 ], "parent" : NumberLong(1001) }

organization has indexes:

db.organization.ensureIndex({"path":1});
db.organization.ensureIndex({"parent":1},{sparse:false});

(note I put awarnes sparse : false - to grant that null is indexed) But, executing:

db.organization.find({"parent":null})

Returns empty set. What is wrong? Thank you in advance

Are you still running into issues with this? Can you give the output of db.system.indexes.find() as well as db.organization.find().explain()? — Barrie
– Barrie, Commented Feb 21, 2012 at 22:23
@Barrie, right now I cannot reproduce this error. I'm pretty sure that it was reached by multiple applying of js scripts from command line tool. Those time I've started explain to recognize error, but it shown normal usage of indexes — Dewfy
– Dewfy, Commented Mar 5, 2012 at 16:12

Daniel K. · Accepted Answer · 2013-04-25 15:18:00Z

19

I had the same issue. After reading the following documents

I tried to query for the different BSON element types and found that my null was represented as a BSON element type 6 (undefined, deprecated) instead of the expected BSON element type 10 (null).

db.collection.find({ field: { "$type" : 6} };

edited Apr 25, 2013 at 15:18

answered Apr 25, 2013 at 14:52

Daniel K.

5,9473 gold badges21 silver badges22 bronze badges

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

5 Comments

AbdelHady Over a year ago

based on the first link, I think it should be db.collection.find({ field: { "$type" : 10} };

mu is too short Over a year ago

@AbdelHady: Perhaps the documents represent a simplified version of reality. I just had to use { field: { $type: 6 } } in an update where I really want to say { field: null }.

mu is too short Over a year ago

@AbdelHady: Scratch that, I think { $type: 10 } really is the right thing and I'm just confusing myself.

AbdelHady Over a year ago

But I tried it myself in my database, {field: null} gave the same result as {field: { "$type" : 10} } but {field: { "$type" : 6} } didn't. And also, the first link in the answer here states that "The { cancelDate : { $type: 10 } } query matches documents that contains the cancelDate field whose value is null only;"

felbus Over a year ago

to find null bson types as @Abdel Hady said, use db.collection.find({ field: { "$type" : 10} };

Andrew Orsich · Accepted Answer · 2012-02-16 13:40:19Z

6

Just checked following script at 2.0 and 2.0.2:

db.items.insert({ "_id" : 1001, "path" : [ ], "parent" : null })
db.items.insert({ "_id" : 1002, "path" : [ 1001 ], "parent" : NumberLong(1001) })
db.items.ensureIndex({"path":1});
db.items.ensureIndex({"parent":1},{sparse:false});
db.items.find({"parent":null})

actually returns one document that you expect:

{ "_id" : 1001,
  "path" : [],
  "parent" : null }

Also you can look into this doc about querying and nulls, probably should help you avoid possible future mistakes.

answered Feb 16, 2012 at 13:40

Andrew Orsich

53.8k17 gold badges142 silver badges134 bronze badges

1 Comment

Dewfy Over a year ago

it is some magic. You example with items works, but mine sucks.

Collectives™ on Stack Overflow

MongoDB queries with null value

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related