4

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.

6
  • What are you trying to achieve with this? Are you generating some sort of a report? Apr 13, 2017 at 9:03
  • Am trying to generate the json file combination of "_Id","ContactId" fields from interactions collection and "Identifier" from the contacts. This json file will generate using the sitcore scheduler.
    – Suresh R
    Apr 13, 2017 at 9:32
  • Why do you need this file? What are you going to use it for? Apr 13, 2017 at 9:33
  • we have the requirement like need to be upload the Json file into CRM for every certain time, please suggest me.
    – Suresh R
    Apr 13, 2017 at 9:46
  • Which version of MongoDB are you using? Apr 13, 2017 at 9:47

1 Answer 1

6

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
            }
        ]
    }
]
2
  • Thank you,am getting "aggregation result exceeds maximum document size (16MB)" exception. Is there any way to restrict some fields. Thanks in advacnce
    – Suresh R
    Apr 13, 2017 at 13:56
  • Please check my code var project = new BsonDocument{{"$project",new BsonDocument{{"Interactions._id",1}}}}; var lookup = new BsonDocument{{"$lookup",new BsonDocument{{"from", "Interactions"},{"localField", "_id"},{"foreignField", "ContactId"},{"as", "Interactions"}}}}; var pipeline = new[] { lookup, project }; var results = contacts.Aggregate(pipeline);
    – Suresh R
    Apr 13, 2017 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.