Creating a partial index when field is not null

user_3183946 0
Viewed 3843

I am trying to create a partial index on a field, but only when the field is not null. In other words, I want to be able to have many of the documents be able to store null, but for documents that actually have a value in the field, I want that value to be unique. Here is the code I tried to use

db.account.createIndex({ "email": 1 }, { unique: true, partialFilterExpression: { "email": { $ne: null } }})

From what I thought I understood, this should index the email field when it is not equal to null.

But it gives the error:

"unsupported expression in partial index: $not email $eq null"

I tried to change $ne: null to $exists: true, but there are already multiple documents with null value for the email field, so it also threw an error. My schema has an email field for the document, so each document will automatically have an email field, meaning $exists: true won't work anyways.

Any help on being able to index a field only when it is not null would be great.

mongodb indexing
2 Answers

The $ne is not supported expression operator,

As per MongoDB Partial Index supported expressions are: equality expressions (i.e. field: value or using the $eq operator), $exists: true expression, $gt, $gte, $lt, $lte expressions, $type expressions, $and operator at the top-level only,

You can use $type to check is string like this { "email": { $type: "string" } }, because null type is "null".

NOTE:

As noted in the query coverage documentation for partial indexes:

Since MongoDB will not use the partial index for a query or sort operation if using the index results in an incomplete result set.

To use the partial index, a query must contain the filter expression (or a modified filter expression that specifies a subset of the filter expression) as part of its query condition.

EX. to use partial index for $type filter you have to use below filter:

{ "email": { "$eq": "foo@mail.com", "$type": "string" } }
// or
{ $and: [{ "email": "foo@mail.com" }, { "email": { $type: "string" } }] }

Playground

user_89871280

Based on inputs from @turivishal, updating here with actual code in the mongo shell.

> db.version()
4.2.6
> db.test2.createIndex({email:1},{unique:true, partialFilterExpression:{email:{$type:"string"}}});
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}
> db.test2.getIndexes();
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "test.test2"
        },
        {
                "v" : 2,
                "unique" : true,
                "key" : {
                        "email" : 1
                },
                "name" : "email_1",
                "ns" : "test.test2",
                "partialFilterExpression" : {
                        "email" : {
                                "$type" : "string"
                        }
                }
        }
]
//note, collection data exists with type "null"/empty values for email for 
//multiple names eg. srini, srini2,srini3 below
> db.test2.find();
{ "_id" : ObjectId("5f6e1c7087ded5e7627c7a19"), "name" : "john", "email" : "john@email.com" }
{ "_id" : ObjectId("5f6e1c7087ded5e7627c7a1a"), "name" : "mary", "email" : "mary@email.com" }
{ "_id" : ObjectId("5f6e1c7087ded5e7627c7a1c"), "name" : "srini" }
{ "_id" : ObjectId("5f6e1c7087ded5e7627c7a1d"), "name" : "kate", "email" : "kate@email.com" }
{ "_id" : ObjectId("5f6e2a2dfbbd49e82b415126"), "name" : "srini2" }
{ "_id" : ObjectId("5f6e2a39fbbd49e82b415127"), "name" : "srini3" }
//write error for duplicate value of email
> db.test2.insertOne({name: "mary1",email:"mary@email.com"});
2020-09-26T00:01:44.941+0530 E  QUERY    [js] WriteError({
        "index" : 0,
        "code" : 11000,
        "errmsg" : "E11000 duplicate key error collection: test.test2 index: email_1 dup key: { email: \"mary@email.com\" }",
        "op" : {
                "_id" : ObjectId("5f6e3790fbbd49e82b415128"),
                "name" : "mary1",
                "email" : "mary@email.com"
        }
}) :
user_141485600
Related