CODEWITHSUNDEEP
databasemongodbgoindexingmongo-go
absolutelydevastated
absolutelydevastated

Reputation: 1747

Setting a field as index only if it is not empty

I have a a structure with two keys as follows:

{
    "cannot_be_empty": "hello",
    "can_be_empty": "world"
}

Right now I set the index to be a combination of can_be_empty and cannot_be_empty.

db.collection.Indexes.CreateOne(IndexModel{{Keys: bson.D{
    {Key: "cannot_be_empty", Value: 1}, 
    {Key: "can_be_empty", Value: 1},
}})

However, I want to make it such that can_be_empty must be unique across all fields unless it is an empty string. Is there a way to do this? I think https://docs.mongodb.com/manual/core/index-partial/ might be the way to go, but I'm not too sure how it works in go.

Thanks!

UPDATE: I've found out that $ne does not work, so I tried this and presently my tests are still failing, but I'll try to find out why.

// Set partial index option
myPartialIndex := options.Index().SetPartialFilterExpression(bson.D{{
    Key:   "can_be_empty",
    Value: bson.D{{Key: "$gt", Value: ""}},
}})
// Set up all indexes
indexes := []mongo.IndexModel{
    {Keys: bson.D{{Key: "cannot_be_empty", Value: 1}, {Key: "can_be_empty", Value: 1}}},
    {Keys: bson.D{{Key: "can_be_empty", Value: 1}}, Options: myPartialIndex},
}
// Create index, no error
db.collection.Indexes().CreateMany(ctx, indexes)

UPDATE 2: I didn't know I had to add a unique option for this to work. In any case, I also need the compound key of can_be_empty and cannot_be_empty to be unique.

FINAL UPDATE: I've accepted the answer, but will also post my own Golang version.

myUniqueIndex := options.Index().SetUnique(true)
myPartialIndex := options.Index().SetPartialFilterExpression(bson.D{{
    Key:   "can_be_empty",
    Value: bson.D{{Key: "$gt", Value: ""}},
}})
// Set up all indexes
indexes := []mongo.IndexModel{
    {Keys: bson.D{{Key: "cannot_be_empty", Value: 1}, {Key: "can_be_empty", Value: 1}}, Options: myUniqueIndex},
    {Keys: bson.D{{Key: "can_be_empty", Value: 1}}, Options: myPartialIndex},
}
// Create index, no error
db.collection.Indexes().CreateMany(ctx, indexes)

Upvotes: 2

Views: 1096

Answers (1)

Tushar Gupta - curioustushar
Tushar Gupta - curioustushar

Reputation: 57095

you can use $gt empty string.

db.collection.createIndex(
   { can_be_empty: 1, cannot_be_empty: 1 },
   { unique: true, partialFilterExpression: { "can_be_empty": {  "$gt": "" }  } }
)

Upvotes: 4

Related Questions