Mongo: Add partial unique index on array of key-value pairs

user_292291 0
Viewed 193

In a collection, I have a metadata field thats just an array of key-value data for generic data.

[
  { key: "test1": value: "value1" },
  { key: "test2": value: "value2" },
  { key: "test3": value: "value3" },
]

Isit possible to add a partial unique index such that I can only one record with key=someId, value=someId. If not, what might be a good way to index such field for more performant search by key value pairs (or do u recommend a different approach). For example:

{
  "name": "xyz",
  "metadata": [
    { key: "oid": value: "value1" }
  ]
},
// This record is valid
{ 
  "name": "abc",
  "metadata": [
    { key: "oid": value: "value2" }
  ]
},
// This record is invalid since value1 is duplicated
{    
  "name": "123",
  "metadata": [
    { key: "oid": value: "value1" } 
  ]
}

UPDATE:

Following @KaushikDas answer, I made an index like suggested

db.collection.metadata.createIndex(
   { metadata: 1 },
   { unique: true,
     partialFilterExpression: { 'metadata.key': 'myid' }
   }
)

It does prevent me from adding duplicate key values, however, when I try to do a query like

db.getCollection('collname').find({
    "metadata" : { $elemMatch: { key: 'myid', value: 'myvalue' } }
}).explain()

It doesnt use the index why is that?

mongodb
2 Answers

You can use something like this.

For Uniqueness

partialFilterExpression

db.collection.metadata.createIndex(
   { metadata: 1 },
   { unique: true, // makes it unique
       partialFilterExpression: { "metadata.key": { $exists: true } }  // index only if metadata is not empty array
   }
)

Assumptions

  • metadata will always have key

Valid

Will allow multiple empty arrays

metadata:[] // record 1 not indexed
metadata:[] // record 2 not indexed

{ "name": "abc", "metadata": [ { key: "oid": value: "value2" } ] } // will be indexed
{ "name": "abc", "metadata": [ { key: "oid": value: "value2" } ] } // will be indexed

Invalid

{ "name": "abc", "metadata": [ { key: "oid": value: "value3" } ] } // will be indexed
{ "name": "abc", "metadata": [ { key: "oid": value: "value3" } ] } // not unique is invalid record

For Query to use index

create a compound index on key and value

db.getCollection.metadata.createIndex({ "metadata.key": 1, "metadata.value": 1 });
user_22242650

Moreover, when you are going to read this collection, please make sure your query predicate should be contain the condition of 'partialFilterExpression', otherwise it will scan whole collection (Index will not honor) and degraded the performance while reading huge data.

Other Views : If you want to alter the schema and want to explore other ways then you have to tell us use case and access pattern of this collection, based on that we do recommend correct data model. Your meta data key value pair is called 'attribute pattern' and this is very useful for eCommerce.

user_44192180
Related