mongodb ignoring sparse on array index

Question

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)?

Tushar Gupta - curioustushar · Accepted Answer · 2021-04-15 08:41:00Z

2

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.

answered Apr 15, 2021 at 8:41

Tushar Gupta - curioustushar

57.2k24 gold badges106 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tcurdt Over a year ago

Thanks! Are you aware of any performance differences when using partial indexes like that?

Tushar Gupta - curioustushar Over a year ago

@tcurdt for making the array unique it's perfect, but you can't leverage this with your query and sorts. For details read - docs.mongodb.com/manual/core/index-partial/#query-coverage

Viktor Andersen · Accepted Answer · 2023-07-04 01:44:38Z

0

@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.

answered Jul 4, 2023 at 1:44

Viktor Andersen

1741 silver badge5 bronze badges

Collectives™ on Stack Overflow

mongodb ignoring sparse on array index

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related