在早期的一些帮助下，我在 SSIS 中创建了一个 C# 脚本，用于将数据从 MongoDB 检索到 SQL Server。虽然很容易检索到常规文档，但嵌套文档和数组却存在问题。

问题 1:我有 shipping_address.country 返回结果使用

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

但是，mlocation.address 使用相同的代码给我一个错误“找不到国家/地区”:

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

问题 2:从数组中检索项目。我有一个数组，看起来像“设备 -> 文档 -> 设备数据 -> 模型”或“设备 -> 文档 -> 设备数据 -> 品牌”。如何在我的代码中检索“型号”或“品牌”值？

非常感谢您的帮助。以下是我的全部代码:

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

对于问题2，我找到了一个有人用过的Java Script；如果我可以将它转换为 C#，它可能会有很大帮助:

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

最佳答案

对于问题 1，你确定所有的文档都包含一个字段 mlocation 是一个包含 country 字段的文档。我能够使用缺少值的文档重现“未找到元素国家/地区”。 例如与

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)

对于问题 2，您不能将 BsonArray 转换为文档。因此，对于上述情况，例如要获取 shipping_address.country，您可以执行类似

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

关于c# - 从 MongoDB 查询嵌套对象(第 2 部分)，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/8732650/