mongodbcompound-index

MongoDB compound sparse indexes


I have a followig compound index:

db.nodes.createIndex( { parent: 1, name: 1 }, { unique: true } );

that index forbides to insert two docs with same name and parent for example:

var n=db.nodes;
n.insert({parent:0,name:"node"});
n.insert({parent:0,name:"node1"});
n.insert({parent:0,name:"node2"});
n.insert({parent:0,name:"node3"});
//throws an error because of compound index:
n.insert({parent:0,name:"node"});

that is ok. Now if name is null (or not present) i want to add multiple docs with same parent (like by sparse single indexes). Is it posible? Example:

n.insert({parent:0,otherattr:"test"});
//throws an error because the tupel {parent:0,name:null} already exists
 n.insert({parent:0,otherattr2:"test"});

Solution

  • You can do this by defining a partial filter expression for your unique index:

    db.nodes.createIndex(
        { parent: 1, name: 1 }, 
        { unique: true,
          partialFilterExpression: {
            name: {$exists: true}
          } 
        });
    

    The filter expression excludes documents without name from the unique index.