MongoDB Composite Key

Question

I'm just getting started with MongoDb and I've noticed that I get a lot of duplicate records for entries that I meant to be unique. I would like to know how to use a composite key for my data and I'm looking for information on how to create them. Lastly, I am using Java to access mongo and morphia as my ORM layer so including those in your answers would be awesome.

Morphia: http://code.google.com/p/morphia/

Solution 1

You can use objects for the _id field as well. The _id field is always unique. That way you kind of get a composite primary key:

 { _id : { a : 1, b: 1} }

Just be careful when creating these ids that the order of keys (a and b in the example) matters, if you swap them around, it is considered a different object.

The other possibility is to leave _id alone and create a unique compound index.

db.things.ensureIndex({firstname: 1, lastname: 1}, {unique: true});
//Deprecated since version 3.0.0, is now an alias for db.things.createIndex()

https://docs.mongodb.org/v3.0/reference/method/db.collection.ensureIndex/

Solution 2

You can create Unique Indexes on the fields of the document that you'd want to test uniqueness on. They can be composite as well (called compound key indexes in MongoDB land), as you can see from the documentation. Morphia does have a @Indexed annotation to support indexing at the field level. In addition with morphia you can define compound keys at the class level with the @Indexed annotation.

Solution 3

I just noticed that the question is marked as "java", so you'd want to do something like:

final BasicDBObject id = new BasicDBObject("a", aVal)
        .append("b", bVal)
        .append("c", cVal);
results = coll.find(new BasicDBObject("_id", id));

I use Morphia too, but have found (that while it works) it generates lots of errors as it tries to marshall the composite key. I use the above when querying to avoid these errors.

My original code (which also works):

final ProbId key = new ProbId(srcText, srcLang, destLang);
final QueryImpl<Probabilities> query = ds.createQuery(Probabilities.class)
  .field("id").equal(key);
Probabilities probs = (Probabilities) query.get();

My ProbId class is annotated as @Entity(noClassnameStored = true) and inside the Probabilities class, the id field is @Id ProbId id;

Comments

[Giovanni Botta]

I have a followup question: how would you query MongoDB to get all records for which 'a' is equal to some value and 'b' can be anything?

[hansvb]

@GiovanniBotta: That's a bit tricky. I forgot the details, but something like "_id > {a: X} and _id < {a: Y}" should work. You can also make a separate index on _id.a

[Giovanni Botta]

actually I figured that out, just query for { "_id.x" : "something" } and it works for any of the fields in the key (although performance varies depending on the ordinality of it).

[eglasius]

@GiovanniBotta I think the approach you mentioned at the end has trouble using the _id index, did you check that?

[Giovanni Botta]

@eglasius Not sure what you mean. Unfortunately I am not working with Mongo anymore so I haven't look at this in a while.

[eglasius]

@GiovanniBotta you mentioned you figured out you could use "_id.x" in the query. If you do that, then you can't take advantage of an index in the "_id" field (the default). Instead you can do like Thilo mentioned, so something like: { "_id" : { $gte: { a : 1 }, $lt: { a : 2 } } }. Syntax might be slightly off, but the point is you use a subdocument to compare against "_id" isntead of going directly against "_id.x"

[Vibhav Chaddha]

Hi, @Nic Cottrell What is 'coll' in coll.find ?

[Vibhav Chaddha]

Thanks, @Nic Cottrell, but your code is not running. It is showing errors. Before db.mycollection.find you had used coll.find and as per my knowledge, it was the variable name in which you had stored your collection. But now in your current code what is mycollection? And how am I suppose to declare this variable? One more thing, new BasicDBObject in results = db.myCollection.find(new BasicDBObjet("_id", id)); is also showing some error. I am a bit new in Java and MongoDB so maybe some of my questions might seem a bit dumb to you, I am apologizing for that in advance. :) Thanks again.

[Nic Cottrell]

Sorry - tried to answer you via my phone and broke my previous answer. You'll need to get an object representing the collection to do the find - will be of class com.mongodb.BasicDBObject...

[Nic Cottrell]

@NewbieSoftwareEngineer I'll need more information about the error before I could help you further.

[Jordan Grant]

Thank you so much for pointing out that composite _id keys field order matters! Due to your help, we've tracked down a bug we introduced in a MongoDB aggregation, where we grouped by fields in a different order than our Spring Data domain object had declared.