CODEWITHSUNDEEP
arraysmongodbindexing
tcurdt
tcurdt

Reputation: 15788

mongodb ignoring sparse on array index

With mongo 4.4.3 I am creating a unique and sparse index into an array:

db.test.createIndex( { "array": 1 }, { unique: true, sparse: true } )

I fill it with some test data:

db.test.insert({})
db.test.insert({array:[]})
db.test.insert({array:[ "a" ]})
db.test.insert({array:[ "b" ]})
db.test.insert({array:[ "c", "d" ]})

Now I would expect the following:

db.test.insert({array:[ "a" ]}) #FAIL
db.test.insert({array:[ "b" ]}) #FAIL
db.test.insert({array:[ "c" ]}) #FAIL
db.test.insert({array:[ "d" ]}) #FAIL
db.test.insert({})              #SUCCESS
db.test.insert({array:[]})      #SUCCESS

Instead I am getting:

db.test.insert({array:[ "a" ]}) #FAIL
db.test.insert({array:[ "b" ]}) #FAIL
db.test.insert({array:[ "c" ]}) #FAIL
db.test.insert({array:[ "d" ]}) #FAIL
db.test.insert({})              #SUCCESS
db.test.insert({array:[]})      #FAIL

With the problem being:

"E11000 duplicate key error collection: db.test index: array_1 dup key: { array: undefined }"

An empty array seems to be treated as undefined - but why is sparse ignored for that?

Is there a way to get the behavior that I expected (without an impact on performance)?

Upvotes: 1

Views: 328

Answers (2)

Viktor Andersen
Viktor Andersen

Reputation: 174

@tcurdt When you make a query to the array, make sure to add the same partial filter expression to the query, i.e.

{array: "my_array_value", "array.0": {$exists: true}}

Otherwise, Mongo will not use the index.

Upvotes: 0

Tushar Gupta - curioustushar
Tushar Gupta - curioustushar

Reputation: 57105

Use https://docs.mongodb.com/manual/core/index-partial/

Partial indexes only index the documents in a collection that meet a specified filter expression. By indexing a subset of the documents in a collection, partial indexes have lower storage requirements and reduced performance costs for index creation and maintenance.

Option 1

db.test.createIndex(
    { "array": 1 },
    { unique: true, partialFilterExpression: { "array": { $gt: [] } } }
)

Create index only if array is gt [] by using partialFilterExpression: { "array": { $gt: [] } }

Option 2

db.test.createIndex(
    { "array": 1 },
    { unique: true, partialFilterExpression: { "array.0": { $exists: true } } }
)

"array.0": { $exists: true } will check if array index 0 is available that is array has some value.

Upvotes: 2

Related Questions