Ensuring index for nested repeating entities

Question

I need to enforce unique constraint on a nested document, for example:

urlEntities: [ 
{ "url" : "http://t.co/ujBNNRWb0y" , "display_url" : "bit.ly/11JyiVp" ,  "expanded_url" :
"http://bit.ly/11JyiVp"} , 
{ "url" : "http://t.co/DeL6RiP8KR" , "display_url" : "ow.ly/i/2HC9x" , 
"expanded_url" : "http://ow.ly/i/2HC9x"}
]

url, display_url, and expaned_url need to be unique. How to issue ensureIndex command for this condition in MongoDB?

Also, is it a good design to have nested documents like this or should I move them to a separate collection and refer them from here inside urlEntities? I'm new to MongoDB, any best practices suggestion would be much helpful.

Full Scenario:

Say if I have a document as below in the db which has millions of data:

{ "_id" : { "$oid" : "51f72afa3893686e0c406e19"} , "user" : "test" , "urlEntities" : [ { "url" : "http://t.co/64HBcYmn9g" , "display_url" : "ow.ly/nqlkP" , "expanded_url" : "http://ow.ly/nqlkP"}] , "count" : 0}

When I get another document with similar urlEntities object, I need to update user and count fields only. First I thought of enforcing unique constraint on urlEntities fields and then handle exception and then go for an update, else if I check for each entry whether it exists before inserting, it will have significant impact on the performance. So, how can I enforce uniqueness in urlEntities? I tried

{"urlEntities.display_url":1,"urlEntities.expanded_url":1},{unique:true}

But still I'm able to insert the same document twice without exceptions.

Subdocument indexing: stackoverflow.com/questions/16769705/subdocument-index-in-mongo — WiredPrairie
– WiredPrairie, Commented Jul 30, 2013 at 4:34
Good design? Really hard to know given so little information. There are lots of possibilities -- it depends on your queries you need, etc. — WiredPrairie
– WiredPrairie, Commented Jul 30, 2013 at 4:35

Derick · Accepted Answer · 2013-07-30 08:32:25Z

1

Uniqueness is only enforced per document. You can not prevent the following (simplified from your example):

db.collection.ensureIndex( { 'urlEntities.url' : 1 } );
db.col.insert( {
    _id: 42,
    urlEntities: [
        { 
            "url" : "http://t.co/ujBNNRWb0y"
        },
        { 
            "url" : "http://t.co/ujBNNRWb0y"
        } 
    ]
});

Similarily, you will have the same problem with a compound unique key for nested documents.

What you can do is the following:

db.collection.insert( {
    _id: 43,
    title: "This is an example",
} );
db.collection.update( 
    { _id: 43 },
    {
        '$addToSet': { 
            urlEntities: { 
                "url" : "http://t.co/ujBNNRWb0y" , 
                "display_url" : "bit.ly/11JyiVp" ,  
                "expanded_url" : "http://bit.ly/11JyiVp"
            }
        }
    }
);

Now you have the document with _id 43 with one urlEntities document. If you run the same update query again, it will not add a new array element, because the full combination of url, display_url and expanded_url already exists.

Also, have a look at the $addToSet query operator's examples: http://docs.mongodb.org/manual/reference/operator/addToSet/

answered Jul 30, 2013 at 8:32

Derick

36.9k7 gold badges82 silver badges104 bronze badges

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

3 Comments

popcoder Over a year ago

I have updated my question with full scenario after reading your answer. The first example you have given is not applicable for my requirements. Through second example, i'm not sure how i can handle insert in document level. Please check the full scenario.

Derick Over a year ago

The first example is just there to illustrate that uniqueness is enforced per document and not for elements in a nested array and very much applicable to your case.

popcoder Over a year ago

Derick, a single document will not have duplicate entries inside urlEntities array. This is possible for different documents only in my case. As you have mentioned that uniqueness can be enforced per document, can you advice me on how to enforce uniqueness between urlEntities array in different document?

pl47ypus · Accepted Answer · 2013-07-29 19:20:41Z

0

for indexes on nested documents read this.

regarding the second part (nested documents best practices) - it really depends on your business logic and queries. if those nested documents don't make sense as first class entities, meaning you won't be searching for them directly but only in the context of their parent document then having them nested make sense. otherwise you should consider extracting them out.

i think that there isn't absolute answer to your question. read the chapter about indexing... it helped me a lot.

answered Jul 29, 2013 at 19:20

pl47ypus

4012 silver badges10 bronze badges

1 Comment

popcoder Over a year ago

I tried db.collection.ensureIndex( { urlEntities.url: 1, urlEntities.display_url: 1, expanded_url= 1 } ) but it throws "syntax error, unexpected token ." Also, can i ensure index when my collection is empty? like how we add unique constraint in a feild in relational database when creating the table?

Collectives™ on Stack Overflow

Ensuring index for nested repeating entities

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related