Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Storing null vs not storing the key at all in MongoDB

It seems to me that when you are creating a Mongo document and have a field {key: value} which is sometimes not going to have a value, you have two options:

  1. Write {key: null} i.e. write null value in the field
  2. Don't store the key in that document at all

Both options are easily queryable, in one you query for {key : null} and the other you query for {key : {$exists : false}}.

I can't really think of any differences between the two options that would have any impact in an application scenario (except that option 2 has slightly less storage).

Can anyone tell me if there are any reasons one would prefer either of the two approaches over the other, and why?

EDIT

After asking the question it also occurred to me that indexes may behave differently in the two cases i.e. a sparse index can be created for option 2.

like image 409
Zaid Masud Avatar asked Sep 13 '12 09:09

Zaid Masud


People also ask

Does MongoDB store null value?

Indeed, it's not possible to store null values in a MongoDB document using a DataFrame. The Python None values are considered as missing attributes accordingly to this NoSQL specific allowance. However, if your column is numerical, you can force writing a null value by setting it to NaN.

WHAT IS NULL value in MongoDB?

The { item : null } query matches documents that either contain the item field whose value is null or that do not contain the item field. The query returns both documents in the collection.

How is NULL value stored?

Remember: If a reference points to null , it simply means that there is no value associated with it. Technically speaking, the memory location assigned to the reference contains the value 0 (all bits at zero), or any other value that denotes null in the given environment.

How set NOT NULL in MongoDB?

Basically, we can use the $exists a method to implement the not null in MongoDB. When <boolean> is valid, $exists coordinates with the records that contain the field, including reports where the field esteem is invalid. In case <boolean> is bogus, the question returns just the records that don't contain the field.


1 Answers

Indeed you have also a third possibility : key: "" (empty value)

And you forget a specificity about null value. Query on key: null will retrieve you all document where key is null or where key doesn't exist.

When a query on $exists:false will retrieve only doc where field key doesn't exist.

To go back to your exact question it depends of you queries and what data represent. If you need to keep that, by example, a user set a value then unset it, you should keep the field as null or empty. If you dont need, you may remove this field.

like image 184
Aurélien B Avatar answered Sep 29 '22 13:09

Aurélien B