How to Join MongoDb Collections Using Sitecore Mongo Db Report Data Source

Question

Am using sitecore 8.0, below code gets the analytics from the mongo db. I have two collections "ineractions" and "ContactId", I want to join both the collections using common field "contact" from interactions collection and "_id" from contacts collection. Please refer my code below:

MongoDbReportDataSource mongoDBSource = new MongoDbReportDataSource("analytics");

string queryInteractions = "{collection: \"Interactions\",query: {_t: \"VisitData\"},fields: [\"_id\",\"ContactId\",\"StartDateTime\",\"Pages.Item._id\",\"Pages.Duration\",\"Pages.Url.Path\",\"Pages.Url.QueryString\",\"Pages.PageEvents.Timestamp\"]}";
ReportDataQuery interactionsReportQuery = new ReportDataQuery(queryInteractions);
DataTable interactions = mongoDBSource.GetData(interactionsReportQuery);

string queryContacts = "{collection: \"Contacts\",fields: [\"_id\",\"Identifiers.Identifier\"]}";
ReportDataQuery contactsReportQuery = new ReportDataQuery(queryContacts);
DataTable contacts = mongoDBSource.GetData(contactsReportQuery);

Please suggest best approach to achieve this.

Answer 1

You can access MongoDB directly through the C# driver:

string connectionString = ConfigurationManager.ConnectionStrings["analytics"].ConnectionString;

var client = new MongoDB.Driver.MongoClient(connectionString);

var database = client.GetServer().GetDatabase("your_database_name");

var contacts = database.GetCollection("Contacts");
var results =
    contacts.Aggregate(
        new BsonDocument
        {
            {
                "$lookup",
                new BsonDocument
                {
                    {"from", "Interactions"},
                    {"localField", "_id"},
                    {"foreignField", "ContactId"},
                    {"as", "Interactions"}
                }
            }
        });

// This may be a very large string, depending on the amount of data you have
string json = results.ResultDocuments.ToJson();

In the mongo shell, you can execute the same query using the following command:

db.Contacts.aggregate({
    $lookup: {
        from: "Interactions",
        localField: "_id",
        foreignField: "ContactId",
        as: "Interactions"
    }
})

This query performs a sort of a join between Contacts and Interactions. An array of Interactions will be nested in every contact object. Here's an example of the output:

[
    {
        "_id" : NUUID("35d21c9d-5135-436f-8da4-fd3cb9fe597d"),
        "System" : {
            "VisitCount" : 2,
            "Value" : 0
        },
        "Lease" : {
            "ExpirationTime" : ISODate("2017-02-10T16:47:39.985Z"),
            "Owner" : {
                "Identifier" : "default-cd-cluster",
                "Type" : 0
            }
        },
        "Interactions" : [ 
            {
                "_id" : NUUID("5395b002-f305-4210-82b1-7f89329f04e8"),
                "_t" : "VisitData",
                "ContactId" : NUUID("35d21c9d-5135-436f-8da4-fd3cb9fe597d"),
                "StartDateTime" : ISODate("2017-02-10T11:46:53.949Z"),
                "EndDateTime" : ISODate("2017-02-10T11:46:53.949Z"),
                "SaveDateTime" : ISODate("2017-02-10T12:07:40.522Z"),
                "ChannelId" : NUUID("b418e4f2-1013-4b42-a053-b6d4dca988bf"),
                "Browser" : {
                    "BrowserVersion" : "56.0",
                    "BrowserMajorName" : "Chrome",
                    "BrowserMinorName" : "56.0"
                },
                "Screen" : {
                    "ScreenHeight" : 480,
                    "ScreenWidth" : 640
                },
                "ContactVisitIndex" : 1,
                "DeviceId" : NUUID("35d21c9d-5135-436f-8da4-fd3cb9fe597d"),
                "Ip" : { "$binary" : "fwAAAQ==", "$type" : "00" },
                "Language" : "en",
                "LocationId" : NUUID("d98c1dd4-008f-04b2-e980-0998ecf8427e"),
                "OperatingSystem" : {
                    "_id" : "WinNT"
                },
                "Pages" : [ 
                    {
                        "DateTime" : ISODate("2017-02-10T11:46:54.189Z"),
                        "Duration" : 0,
                        "Item" : {
                            "_id" : NUUID("110d559f-dea5-42ea-9c1c-8a5df7e70ef9"),
                            "Language" : "en",
                            "Version" : 1
                        },
                        "PageEvents" : [ 
                            {
                                "Name" : "Long running request",
                                "ItemId" : NUUID("110d559f-dea5-42ea-9c1c-8a5df7e70ef9"),
                                "Timestamp" : NumberLong(0),
                                "Data" : "7448",
                                "DataKey" : "7,449",
                                "Text" : "Request took 7,449ms to complete",
                                "PageEventDefinitionId" : NUUID("dc6f6aff-6aa9-423f-a824-49f9ee741aa9"),
                                "DateTime" : ISODate("2017-02-10T11:46:57.028Z"),
                                "Value" : 0
                            }
                        ],
                        "PersonalizationData" : {
                            "ExposedRules" : []
                        },
                        "SitecoreDevice" : {
                            "_id" : NUUID("fe5d7fdf-89c0-4d99-9aa3-b5fbd009c9f3"),
                            "Name" : "Default"
                        },
                        "MvTest" : {
                            "ValueAtExposure" : 0
                        },
                        "Url" : {
                            "Path" : "/"
                        },
                        "VisitPageIndex" : 1
                    }
                ],
                "SiteName" : "website",
                "TrafficType" : 20,
                "UserAgent" : "Mozilla/5.0 (Windows NT 6.3; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36",
                "Value" : 0,
                "VisitPageCount" : 1
            }
        ]
    }
]