Mongoose, set default to null and unique

Question

I am trying to create a User Schema that has a key of 'facebookid', which is a unique value that is only given when a user signs up with facebook, but not when a user signs up through google or locally. When a user signs up locally, the facebookid is set to null as a default. However I am getting the error: 'insertDocument :: caused by :: 11000 E11000 duplicate key error index: db22.users.$facebookid_1 dup key: { : null }'

Here is the Schema:

let UserSchema = new Schema({
    facebookid: {
        type: String,
        required: false, // only required for facebook users
        unique: true,
        default: null
    },
    // more details + keys below ...
})

So how can I allow duplicate entries for the key facebookid if the value is null if I also want entries to be unique?

i.e I don't want to see two similar String entries:
not okay:

* User: {_id: 123, facebookid: '4325',username:'joe'}
* User: {_id: 432, facebookid: '4325', username: 'pat'}

okay:

* User: {_id: 123, facebookid: null,username:'joe'}
* User: {_id: 432, facebookid: null, username: 'pat'}

You have to set unique to false – Sombrero Nov 21 '17 at 07:23 — Sombrero, Nov 21 '17 at 07:23

score 14 · Answer 1 · answered Nov 21 '17 at 13:02

14

You can use partialFilterExpressions:

facebookid: {
  type: String,
  required: false, // only required for facebook users
  index: {
    unique: true,
    partialFilterExpression: { facebookid: { $type: 'string' } },
  },
  default: null,
 }

This allows null to be used several times.

Remember to restart Mongoose and all that after changing the unique constraints. See this question if you haven't already.

answered Nov 21 '17 at 13:02

Mika Sundland

18,120
16
38
50

Thanks @MikaS for this. BTW, mongo restart didn't help, I drop database and after that it started working, do you know why? – Boris Savic Jun 24 '18 at 17:56
so what's the difference between the approach of using partialFilterExpression and using sparse: false? – Normal Jun 27 '22 at 05:40

score 3 · Answer 2 · answered Jun 15 '18 at 00:03

Thanks @Mikas!

Extra bonus - you can refer nested keys as follows: "key1.key2".
Unfortunately, $ne is not supported (yet?), so you cannot use simply: $ne: null

    key1: {
        key2: {
            type: String,
            index: {
                unique: true,
                partialFilterExpression: {
                    "key1.key2": {
                        // $ne: null,
                        $type: Schema.Types.String
                    }
                },

            },
            default: null
        }

score 2 · Answer 3 · answered Nov 21 '17 at 07:33

For that situation you should not use unique: true in schema but you have to check facebookid is already exist or not so you can add validation for facebookid using schemaName.path and check uniqueness. like bellow.

let UserSchema = new Schema({
    facebookid: {
        type: String,
        required: false,
        default: null
    },
    // more details + keys below ...
})

UserSchema.path('facebookid').validate(function(value, next){
  if(!value) {
    return next();
  }
  // I assueme your model name is User
  mongoose.models['User'].findOne({facebookid: value }, function(err, found){
    if(err){
      return next(false);
    }

    if(found && found.facebookid){
      return next(false, "Already exist this facebook ID");
    }else{
      return next();
    }
  });

});

Mongoose, set default to null and unique

3 Answers3

Linked