MongoDb - "Join" Collections

Question

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"
  } 
}
]);

https://stackoverflow.com/questions/68155715/how-do-i-write-this-sql-query-in-mongodb-syntax/68549980#68549980 — KushalSeth, Jul 27 '21 at 18:34

score 1 · Accepted Answer · answered Oct 30 '17 at 21:29

The only ways to combine multiple collections or to do any reshaping of the data e.g grouping, ungrouping (unwind) is to either using the aggregation framework (recommended) or the older map reduce framework (less recommended).

Specifically, while aggregating you have various aggregation steps to choose from including $lookup.

From a brief look at your data, you need to aggregate the MessageEvent collection and lookup data in the Message collection (since the IDs point from the MessageEvent to the Message objects and not vice versa).

You have $project, $group stages which can help you reshape the data.

MongoDb - "Join" Collections

1 Answers1