Given the following collections:
Message collection
{ "_id" : ObjectId("59f76fc3a8e87e411c22d0ac"), "OriginApp" : "App1", "MsgGroupId" : "499", "UserName" : "User1", "Message" : "Test Message [Group ID: 499]" } { "_id" : ObjectId("59f76fc3a8e87e411c22d0ad"), "OriginApp" : "App1", "MsgGroupId" : "499", "UserName" : "User2", "Message" : "Test Message [Group ID: 499]" } { "_id" : ObjectId("59f76fc3a8e87e411c22d0af"), "OriginApp" : "App1", "MsgGroupId" : "499", "UserName" : "User3", "Message" : "Test Message [Group ID: 499]" } MessageEvent collection
{ "_id" : ObjectId("59f772d1d22ea83b249d19c2"), "Event" : "Send", "Msg" : { "UserName" : "User1", "Metadata" : { "OriginApp" : "App1", "MessageId" : "59f76fc3a8e87e411c22d0ac" }, }, "TimeStamp" : "2017-10-30T18:20:17Z" } { "_id" : ObjectId("59f772d1d22ea83b249d19c2"), "Event" : "Open", "Msg" : { "UserName" : "User1", "Metadata" : { "OriginApp" : "App1", "MessageId" : "59f76fc3a8e87e411c22d0ac" }, }, "TimeStamp" : "2017-10-30T18:30:16Z" } { "_id" : ObjectId("59f772d1d22ea83b249d19c2"), "Event" : "Click", "Msg" : { "UserName" : "User1", "Metadata" : { "OriginApp" : "App1", "MessageId" : "59f76fc3a8e87e411c22d0ac" }, }, "TimeStamp" : "2017-10-30T18:38:57Z" } I would like to return a projection that looks like the following:
{ "MessageId" : "59f76fc3a8e87e411c22d0ac" "EventId" : "59f772d1d22ea83b249d19c2", "Event" : "Send", "UserName" : "User1", "OriginApp" : "App1", "TimeStamp" : "2017-10-30T18:20:17Z" } { "MessageId" : "59f76fc3a8e87e411c22d0ac" "EventId" : "59f772d1d22ea83b249d19c2", "Event" : "Open", "UserName" : "User1", "OriginApp" : "App1", "TimeStamp" : "2017-10-30T18:30:16Z" } { "MessageId" : "59f76fc3a8e87e411c22d0ac" "EventId" : "59f772d1d22ea83b249d19c2", "Event" : "Click", "UserName" : "User1", "OriginApp" : "App1", "TimeStamp" : "2017-10-30T18:38:57Z" } I ran into problems right away because the MsgEvent.Msg.Metadata.MessageId is a string while Message._id is an ObjectId. It is not possible to have create MsgEvent.Msg.Metadata.MessageId as an ObjectId because it is returned to a service by an external application as simple json.
Is there a way to use the distinct operator or some type of aggregation to accomplish the desired result?
-- UPDATE --
Given that MongoDb will not allow for data conversion in queries (which was part of the original question), I have solve the issue for now by supplying an additional attribute which is a Guid as a string:
Message collection
{ "_id" : ObjectId("59f76fc3a8e87e411c22d0ac"), "GuidString" : "d273a5dd-7291-432f-a9ab-8bad7343a1a4", "OriginApp" : "App1", "MsgGroupId" : "499", "UserName" : "User1", "Message" : "Test Message [Group ID: 499]" } MessageEvent collection
{ "_id" : ObjectId("59f772d1d22ea83b249d19c2"), "Event" : "Send", "Msg" : { "UserName" : "User1", "Metadata" : { "OriginApp" : "App1", "MessageId" : "59f76fc3a8e87e411c22d0ac", GuidString" : "d273a5dd-7291-432f-a9ab-8bad7343a1a4" }, }, "TimeStamp" : "2017-10-30T18:20:17Z" } etc...
The following aggregation will return the expected results:
db.MessageEvent.aggregate( [ { $match : { "Msg.Metadata.GuidString" : "d273a5dd-7291-432f-a9ab-8bad7343a1a4" } }, { $unwind: "$Msg" }, { $lookup: { "from": "GuidString", "localField": "ChannelSenderId", "foreignField": "GuidString", "as": "messages" } } ]); 
