MongoDB 查询数组 .NET MVC 中的文档

Question

使用MongoDB集合结构，如以下示例所示：

{
    "_id" : "123",
    "memberinfo" : [{
        "height": 170,
        "weight": 55,
        "status": "approved",
        "fruits" : [ {"name" : "apple","reason" : null },
                     {"name" : "orange","reason" : null},
                     {"name" : "berry","reason" : null}
        ],
    }]
},
{
    "_id" : "456",
    "memberinfo" : [{
        "height" : 160,
        "weight": 90,
        "status": "approved",
        "fruits" : [ {"name" : "berry","reason" : null},
                     {"name" : "orange","reason" : null}
        ],
    },{
        "height" : 160,
        "weight": 90,
        "status": "rejected",
        "fruits" : [ {"name" : "banana","reason" : null}
        ],
    }]
}

如何查询嵌套数组以获取具有status:approved的memberinfo。

结果应该是这样的：

{
    "_id" : "123",
    "memberinfo" : [{
        "height": 170,
        "weight": 55,
        "status": "approved",
        "fruits" : [ {"name" : "apple","reason" : null },
                     {"name" : "orange","reason" : null},
                     {"name" : "berry","reason" : null}
        ],
    }]
},
{
    "_id" : "456",
    "memberinfo" : [{
        "height" : 160,
        "weight": 90,
        "status": "approved",
        "fruits" : [ {"name" : "berry","reason" : null},
                     {"name" : "orange","reason" : null}
        ],
    }]
}

我试过这个，但结果不对：

IMongoQuery query = Query.And(Query.EQ("memberinfo.status", "approved"));
MongoCursor mongocursor = nsdb.GetCollection(DBPrefix, "Member").Find(query);

Answer 1

查询非常简单; 您需要使用 $ 位置运算符指定投影，以确保查询仅返回与结果查询匹配的数组项：

db.Member.find(
  { "memberinfo.status" : "approved" },
  {
    "_id" : 1, 
    "memberinfo.$" : 1
  }
);

注意：这是查询的本机MongoDB版本;恐怕我不熟悉应该如何使用 C# 驱动程序生成它。

---

C# 更新 - Elemmatch 版本(与位置运算符相同([归功于 Veerlam]。

当每个memberinfo数组中只有一个 approved status 元素时，此解决方案有效。

IMongoFields fields = Fields.Include("_id").ElemMatch("memberinfo", Query.EQ("status", "approved"));
MongoCursor mongocursor = nsdb.GetCollection(DBPrefix, "Member").Find((Query.Empty).SetFields(fields);

Answer 2

Vince 提供了使用投影和位置运算符的解决方案。

下面给出了使用聚合管道以及$unwind和$match的替代解决方案

$unwind - 用于将数组元素分解为单独的文档

让我们收集文件

db.collection.find()
    {
    "_id" : "123",
    "memberinfo" : [{
        "height": 170,
        "weight": 55,
        "status": "approved",
        "fruits" : [ {"name" : "apple","reason" : null },
                     {"name" : "orange","reason" : null},
                     {"name" : "berry","reason" : null}
        ],
    }]
},
{
    "_id" : "456",
    "memberinfo" : [{
        "height" : 160,
        "weight": 90,
        "status": "approved",
        "fruits" : [ {"name" : "berry","reason" : null},
                     {"name" : "orange","reason" : null}
        ],
    },{
        "height" : 160,
        "weight": 90,
        "status": "rejected",
        "fruits" : [ {"name" : "banana","reason" : null}
        ],
    }]
}

使用$unwind后

db.collection.aggregate([{$unwind: "$memberinfo"}])
{
        "_id" : "123",
        "memberinfo" : {
                "height" : 170,
                "weight" : 55,
                "status" : "approved",
                "fruits" : [
                        {
                                "name" : "apple",
                                "reason" : null
                        },
                        {
                                "name" : "orange",
                                "reason" : null
                        },
                        {
                                "name" : "berry",
                                "reason" : null
                        }
                ]
        }
}
{
        "_id" : "456",
        "memberinfo" : {
                "height" : 160,
                "weight" : 90,
                "status" : "approved",
                "fruits" : [
                        {
                                "name" : "berry",
                                "reason" : null
                        },
                        {
                                "name" : "orange",
                                "reason" : null
                        }
                ]
        }
}
{
        "_id" : "456",
        "memberinfo" : {
                "height" : 160,
                "weight" : 90,
                "status" : "rejected",
                "fruits" : [
                        {
                                "name" : "banana",
                                "reason" : null
                        }
                ]
        }
}

现在我们已经成功地展开了memberinfo数组，以便每个文档只有一个memberinfo.status

现在，在聚合管道中使用$match来获得所需的结果

Final Mongo Shell Query： db.collection.aggregate([{$unwind： "$memberinfo"}， {$match：{"memberinfo.status"："approved"}}](

db.collection.aggregate([{$unwind: "$memberinfo"}, {$match:{"memberinfo.status":"approved"}}])
{
        "_id" : "123",
        "memberinfo" : {
                "height" : 170,
                "weight" : 55,
                "status" : "approved",
                "fruits" : [
                        {
                                "name" : "apple",
                                "reason" : null
                        },
                        {
                                "name" : "orange",
                                "reason" : null
                        },
                        {
                                "name" : "berry",
                                "reason" : null
                        }
                ]
        }
}
{
        "_id" : "456",
        "memberinfo" : {
                "height" : 160,
                "weight" : 90,
                "status" : "approved",
                "fruits" : [
                        {
                                "name" : "berry",
                                "reason" : null
                        },
                        {
                                "name" : "orange",
                                "reason" : null
                        }
                ]
        }
}

希望对您有所帮助！

引用：

https://docs.mongodb.com/getting-started/csharp/aggregation/

http://mikaelkoskinen.net/post/mongodb-aggregation-framework-examples-in-c