[SERVER-45615] Ability to add sparse index on fields in an array of documents Created: 16/Jan/20  Updated: 04/Feb/20  Resolved: 04/Feb/20

Status: Closed
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: jackson millsaps Assignee: Carl Champain (Inactive)
Resolution: Done Votes: 0
Labels: qopt-team
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Participants:
Case:

 Description   

When an index is created on a field within an array, each the value of the field for each array item gets indexed. There is no way to use 'sparse' or a 'partialFilterExpression' to prevent nulls from being added for every item in the array that is missing the field. This is a problem when you want the index to be unique, since only one item in the array can (across all docs) can be missing the field.

In the example, I would like a unique index on 'logins.auth_details.username' , but because both Alice and Casper have type=google logins, this isn't possible.

db.users.insertMany( 
 { 
 
    display_name:'Alice',
        logins: [
        { 
            type:'local',
            auth_details: { 
                username:'alice',
                password:'<hashed password here>',
            },
        },
        { 
 
            type:'google',
            auth_details: { 
                google_id:'123',
            },
        },
    ],
},
{ 
 
    display_name:'Bob',
    logins: [
        { 
        type:'local',
        auth_details: { 
            username:'bob',
            password:'<hashed password here>',
            },
        },
    ],
},
{ 
    display_name:'Casper',
    logins: [
         { 
            type:'local',
            auth_details: { 
                username:'Casper,
                password:'<hashed password here>',
            },
        },
        { 
           type:'google',
           auth_details: { 
               google_id:'456',
            },
        },
    ],
},

 



 Comments   
Comment by Carl Champain (Inactive) [ 04/Feb/20 ]

jmillsaps@datatrac.com,

Sorry, I misunderstood your initial statement.
It's possible to combine unique and sparse to accomplish the stated goal:

db.users.createIndex({"logins.auth_details.google_id":1},{unique:true, sparse:true})
db.users.createIndex({"logins.auth_details.username":1},{unique:true, sparse:true})

 As this ticket does not appear to be a bug, I will now close it. 

Kind regards,
Carl

Comment by Carl Champain (Inactive) [ 24/Jan/20 ]

Hi jmillsaps@datatrac.com,

Thank you for the report.
I'm passing this ticket along to the appropriate team for further investigation. Updates will be posted on this ticket as they happen.

Kind regards,
Carl

Generated at Thu Feb 08 05:09:17 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.