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);

Sridhar Nanjundeswaran · Accepted Answer · 2012-01-04 23:15:58Z

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;
}

edited Jan 4, 2012 at 23:15

answered Jan 4, 2012 at 22:01

Sridhar Nanjundeswaran

7543 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Pasha Over a year ago

Sridhar, thank you! However, shipping_address does not appear to be an array as your code suggest. If I use your code, it throws a cast error (can't cast BsonDocument to BsonArray... I understand that for Problem 2 I can't cast BsonArray as document, but how can I retrieve items from this array, i.e. assign device.device_data.model to a variable?

Sridhar Nanjundeswaran Over a year ago

Can you attach a sample document? In my sample shipping_address is an array of sub documents with the first one contain 2 elements and the 2nd one

Pasha Over a year ago

Here is what I did to make it work (Problem 1), please let me know if it is the best approach: var mlocation = document["mlocation"].AsBsonDocument; var country = ""; if (mlocation != null && mlocation.Contains("country")) { country = mlocation.AsBsonDocument.GetValue("country").ToString(); }

Robert Stam Over a year ago

I added a new answer so I could format some code in my response.

Robert Stam · Accepted Answer · 2012-01-05 03:48:55Z

0

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.

answered Jan 5, 2012 at 3:48

Robert Stam

12.2k2 gold badges41 silver badges36 bronze badges

4 Comments

Pasha Over a year ago

Breaking it down to steps is exactly what I need, as this entire MongoDB and C# concepts are new to me, so big thank you! :) Your approach works; however, as I understand, some records do not have all the items; therefore, the code throws the 'Index was out of range' error. How do I check for this using your example? Again, big thanks for your example, it helps a lot!

Pasha Over a year ago

To check the array, I cast it to List and counted a number of items. It works and I hope it is an efficient approach.

Pasha Over a year ago

Robert Stam Over a year ago

I added a new answer so I could format some code in my response.

Sridhar Nanjundeswaran · Accepted Answer · 2012-01-05 07:13:22Z

0

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)

answered Jan 5, 2012 at 7:13

Sridhar Nanjundeswaran

7543 silver badges3 bronze badges

Comments

Robert Stam · Accepted Answer · 2012-01-05 20:13:50Z

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
    }
}

Collectives™ on Stack Overflow

Query nested objects from MongoDB (Part 2)

4 Answers 4

4 Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related