Query nested objects from MongoDB (Part 2)

Question

With some earlier help, I created a C# script in SSIS to retrieve data from MongoDB to SQL Server. While regular documents are retrieved easily, nested documents and arrays are problematic.

Problem 1: I have shipping_address.country that returns results by using

this.UserDBBuffer.SCountry = document["shipping_address"].AsBsonDocument["country"].ToString();

However, mlocation.address gives me an error '"country" not found' using the same code:

this.UserDBBuffer.Country = document["mlocation"].AsBsonDocument["country"].ToString();

Problem 2: Retrieving items from arrays. I have an array that looks like "devices -> Document -> device_data -> model" or "devices -> Document -> device_data -> brand". How do I retrieve "model" or "brand" values in my code?

Thanks a lot for your help. Below is my entire code:

public override void CreateNewOutputRows()
{
    string connectionString = "mongodb://localhost";
    MongoServer myMongo = MongoServer.Create(connectionString);
    myMongo.Connect();
    var db = myMongo.GetDatabase("UserDB");

    //Declaring variables for Date Created conversions
    string DateCreatedString;
    DateTime DateCreatedDateUTC;
    DateTime DateCreatedDateLocal;

    var fields = Fields.Include("mlocation.country", "mlocation", "_id", "primary_email", "gender", "date_created");
    var collection = db.GetCollection<BsonDocument>("users");

    foreach (var document in collection.FindAll().SetFields(fields))
    {
        this.UserDBBuffer.AddRow();
        this.UserDBBuffer.ID = document["_id"] == null ? "" : document["_id"].ToString();
        this.UserDBBuffer.Country = document["mlocation"].AsBsonDocument["country"].ToString();
        this.UserDBBuffer.PrimaryEmail = document["primary_email"] == null ? "" : document["primary_email"].ToString();
        this.UserDBBuffer.Gender = document["gender"] == null ? "" : document["gender"].ToString();

        //Importing Date Created as String for data manipulation
        DateCreatedString = document["date_created"] == null ? "" : document["date_created"].ToString();
        //First, making sure that we have a UTC datetime
        DateCreatedDateUTC = DateTime.Parse(DateCreatedString).ToUniversalTime();

        //Second, converting to Local Time
        DateCreatedDateLocal = DateTime.Parse(DateCreatedString).ToLocalTime();

        //Finally, assigning variables to rows
        this.UserDBBuffer.DateTimeCreatedUTC = DateCreatedDateUTC;
        this.UserDBBuffer.DateTimeCreatedLocal = DateCreatedDateLocal;
    }

    myMongo.Disconnect();
}

For Problem 2, I found a Java Script that someone used; if I can convert it to C#, it might help a lot:

count = 0;

function user_list(){
    var cursor = db.users.find()

    //var cursor = db.users.find({"devices": {"$ne":[]}})
    cursor.forEach(function(user) {
        var deviceInfo = "";
        if (user.devices){
            if (user.devices[0]){
                dd = user.devices[0].device_data; 
                if (dd) {
                    deviceInfo = dd.model + "," + dd.brand  + "," + dd.model + "," + dd.device + "," + dd.pixel_height + "," + dd.pixel_width + "," + dd.pixel_format;
                }
            }
        }
        var location = "";
        if (user.mlocation) location = user.mlocation.country;
        print(user._id + "," + location + "," + user.primary_email + "," + user.date_created + "," + deviceInfo);
        count++;
    });
}
user_list();
print(count);

Answer 1

For problem 1, are you sure all docs contain a field mlocation that is a document containing the country field. I was able to reproduce the "Element country not found" with a document that is missing the value. e.g. with

db.users.find() { "_id" : ObjectId("4f04c56a0f8fa4413bed1078"), "primary_email" : "[email protected]", "shipping_address" : [ {"country" : "USA", "city" : "San Francisco" }, { "country" : "IN", "city" : "Chennai" } ], "mlocation" : { "country" : "Canada", "city" : "Montreal" } } { "_id" : ObjectId("4f04d1605ab5a3805aaa8666"), "primary_email" : "[email protected]", "shipping_address" : [ { "country" : "MX", "city" : "Cabo San Lucas" } ], "mlocation" : { "city" : "Montreal" } } the 2nd document throws the exception. You can either check for its existance or use the default value option document["mlocation"].AsBsonDocument.GetValue("country", null)

For problem 2, you cannot cast a BsonArray as a document. So for the above e.g to get shipping_address.country you can do something like

foreach (var addr in document["shipping_address"].AsBsonArray)
{
    var country = addr.AsBsonDocument["country"].AsString;
}