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

Instead of:

var mlocation = document["mlocation"].AsBsonDocument;
var country = "";
if (mlocation != null && mlocation.Contains("country"))
{
    country = mlocation.AsBsonDocument.GetValue("country").ToString();
}

I would write:

var mlocation = document["mlocation"].AsBsonDocument;
var country = "";
if (mlocation.Contains("country"))
{
    country = mlocation["country"].AsString;
}

And instead of:

var devices = document["devices"].AsBsonArray;
if (devices.ToList().Count > 0)
{
    if (devices[0].AsBsonDocument != null)
    {
        var deviceinfo = devices[0].AsBsonDocument;
        if (deviceinfo["device_data"].AsBsonDocument != null)
        {
            var deviceData = deviceinfo["device_data"].AsBsonDocument;
            model = deviceData.GetValue("model", null).AsString;
        }
    }
}

I would write:

var devices = document["devices"].AsBsonArray;
if (devices.Count > 0)
{
    var deviceinfo = devices[0].AsBsonDocument;
    if (deviceinfo.Contains("device_data"))
    {
        var deviceData = deviceinfo["device_data"].AsBsonDocument;
        var model = deviceData.GetValue("model", "").AsString; // "" instead of null
    }
}

Answer 2

To clarify your comment to Robert's answer, you can use BsonDocument.Contains to check if a given BsonDocument contains a field of the specified name before getting its value (http://api.mongodb.org/csharp/current/html/6181f23f-f6ce-fc7d-25a7-fc682ffd3c04.htm)

Answer 3

Assuming the devices element is an array, just drill your way down into the element you are looking for, like this:

BsonDocument document; // assume this comes from somewhere
var devices = document["devices"].AsBsonArray;
var device = devices[0].AsBsonDocument; // first element of array
var deviceData = device["device_data"].AsBsonDocument;
var model = deviceData["model"].AsString;
var brand = deviceData["brand"].AsString;

I've broken it down into steps for clarity, but you can combine some of these steps into longer statements if you want.

Answer 4

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@email.com", "shipping_address" : [ {"country" : "USA", "city" : "San Francisco" }, { "country" : "IN", "city" : "Chennai" } ], "mlocation" : { "country" : "Canada", "city" : "Montreal" } } { "_id" : ObjectId("4f04d1605ab5a3805aaa8666"), "primary_email" : "incorrect@email.com", "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;
}