8/07/2013

Query MongoDB with DBRef (xxx: {"$ref":"XXX", "$id": ObjectId("XXX") })

When we have database reference in Mongo Collection. How can we query the record based on the reference id?

Reference:
http://docs.mongodb.org/manual/reference/database-references/

Basically we can query in this way:

For example we have record like this:

Collection : ChannelCfg has this kind of records:

{
  "_id" : ObjectId("51e71075956c1989446b9333"),
  "_class" : "com.test.channel.ConfigEntity",
  "eventTypeId" : "51e71066956c1989446b9213",
  "templateId" : "51e71067956c1989446b922e",
  "createdTime" : ISODate("2013-07-17T21:45:25.073Z"),
  "channelType" : {
    "$ref" : "ChannelType",
    "$id" : ObjectId("51e71064956c1989446b9210")
  }
}

If we want to query the record with specific eventTypeId e.g. "51e71066956c1989446b9213" and specific channelType with id, e.g. "51e71064956c1989446b9210" and assume that this result is unique.

We can make the query:

var event = db.EventType.findOne({"eventType":"TEST_TYPE"});
var emailChannel = db.ChannelType.findOne({"channelType":"SMTP"});
var channelConfig = db.ChannelCfg.findOne( { $and : [{"eventTypeId": event._id.toString()} , {"channelType.$id": emailChannel._id}] });


Note:

In this case the _id in all the collections are all ObjectId type. We can see that in this case, because the 'eventTypeId' in ChannelCfg collection is one String and the event._id is ObjectId type, so we need to make convert "event._id.toString()"(For version 2.0.X)(If it's new version 2.4.x, should use valueOf() instead). And the channelType's "$id" is ObjectId type and we do not need to convert the emailChannel._id.


1 comment: