I am trying to create a index with schema

{
   _id: "",
   name: ""
}

I want to make an index that supports names with unique values and permits adding data to databases when names have the value "" (empty string).

I tried using this:

db.collection.createIndex(
   {name: 1}, 
   {unique: true, partialFilterExpression: {name: {$type: "string"}}}
)

CodePudding user response：

An empty string "" is still a string, so partialFilterExpression: {name: {$type: "string"}} will index this document.

In principle, it would be this one:

db.collection.createIndex(
   {name: 1}, 
   {unique: true, partialFilterExpression: {name: {$ne: ""}}}
)

However, partialFilterExpression supports only these operators:

• equality expressions (i.e. field: value or using the $eq operator),
• $exists: true expression,
• $gt, $gte, $lt, $lte expressions,
• $type expressions,
• $and operator,
• $or operator,
• $in operator

Looks like, this is not possible. Only way would be to skip the attribute and use { partialFilterExpression: { name: { $exists: true } } }

CodePudding user response：

You can use the string sort ordering to skip the empty string.

Have the partial filter require that name be type string and greater than "":

partialFilterExpression: {$and: [
                                  {name: {$type: "string"}},
                                  {name: {$gt: ""}}
                          ]}