Databases
StackOverflow2013
Postsdbo
POWhen do I use null value for a field and when should I not write the field in MongoDB?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhen do I use null value for a field and when should I not write the field in MongoDB?
    primarykey
    data
    text
    <p>I'm currently playing around with reactivemongo and mongodb and I found something I do not know how to handle.</p> <p>Let say I have an case class <strong>UserInfo</strong>:</p> <pre><code>case class UserInfo(override var _id: Option[BSONObjectID] = None, firstName: String, lastName: Option[String], email: Option[String], mobile: Option[String], fbId: Option[String], fbPublish: Option[Boolean]) { } </code></pre> <p>this object represent an user. The user has always a firstName and might have an email or mobile number.</p> <pre><code>object UserInfo { import play.modules.reactivemongo.json.BSONFormats._ implicit val userInfoReads: Reads[UserInfo] = ( (__ \ "_id").read[Option[BSONObjectID]] and (__ \ "firstName").read[String] and (__ \ "lastName").read[Option[String]] and (__ \ "email").read[Option[String]] and (__ \ "mobile").read[Option[String]] and (__ \ "fbId").read[Option[String]] and (__ \ "fbPublish").read[Option[Boolean]] )(UserInfo.apply _) implicit val userInfoWrites: Writes[UserInfo] = ( (__ \ "_id").write[Option[BSONObjectID]] and (__ \ "firstName").write[String] and (__ \ "lastName").write[Option[String]] and (__ \ "email").write[Option[String]] and (__ \ "mobile").write[Option[String]] and (__ \ "fbId").write[Option[String]] and (__ \ "fbPublish").write[Option[Boolean]] )(unlift(UserInfo.unapply)) } </code></pre> <p>In the UserInfo companion object I define the writes and reads for UserInfo. When inserting this document the UserInfo is saved to my Mongo instance and the fields that are None is saved as null. Since I have a lot of users I would like to set a index on <strong>firstName + email</strong> and a second index on <strong>firstName + mobile</strong>:</p> <pre><code>ensureIndex(List("firstName" -&gt; IndexType.Ascending, "email"-&gt; IndexType.Ascending), unique = true, sparse = true) ensureIndex(List("firstName" -&gt; IndexType.Ascending, "mobile"-&gt; IndexType.Ascending), unique = true, sparse = true) </code></pre> <p>I insert an UserInfo with firstName and email. Rest of the fields are None.</p> <p>And now comes the part I do not know how to handle. Since None writes to null the null value is saved in my collection of userinfos. I try to insert the same firstName and email, this should fail and it does, but for the wrong reason. The reason in my case is that there is an index created for jakob and null:</p> <pre><code>DB operation failed: [message=DatabaseException['E11000 duplicate key error index: userinfos.$userInfo.userInfo.mobile_1 dup key: { : "jakob", : null }' (code = 11000)]] </code></pre> <p>As I understand sparse only works for missing values and since null is a value, sparse do not work in this case. So how should I handle this? Is it possible to create indexes with values that could be null? Or should I treat my None values in some other way? Maybe I could change the writes so that if None write empty [T] param. What is the MongoDB way of handling this?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USjakob
    1. USjakob
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COThis has been answered....well many times if you google
      singulars
      1. POWhen do I use null value for a field and when should I not write the field in MongoDB?
      1. USSammaye
    2. COHow about you pointing me to that answer?
      singulars
      1. POWhen do I use null value for a field and when should I not write the field in MongoDB?
      1. USjakob
    3. COFound this: https://jira.mongodb.org/browse/SERVER-2193 Kind of old. But I guess that this means that mongodb still do not support sparse on compound indexes
      singulars
      1. POWhen do I use null value for a field and when should I not write the field in MongoDB?
      1. USjakob
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload