MongoDB对嵌套文档列表的查询 [英] MongoDB query on list of nested documents

问题描述

我不是MongoDB的新手，而是聚合概念的新手... 我有看起来像这样的收集数据，目前它包含2个文档

I am not a newbie to MongoDB but new to aggregation concepts... I have collection data which looks something like this, currently it contains 2 documents

 {
    "_id" : ObjectId("52cc0b079f0ae55e9fb770f8"),
    "uid" : 100,
    "data" : {
        "mi" : [ 
            {
                "miId" : NumberLong(1),
                "name" : "ABC",
                "severity" : "HIGH",
                "failures" : NumberLong(2),
                "description" : "Some description",
                "remediation" : "Some remedy"
            }, 
            {
                "miId" : NumberLong(10),
                "name" : "PQR",
                "severity" : "HIGH",
                "failures" : NumberLong(3),
                "description" : "Some description",
                "remediation" : "Some remedy"
            }
}

{
    "_id" : ObjectId("52cc0b079f0ae55easdas8"),
    "uid" : 200,
    "data" : {
        "mi" : [ 
            {
                "miId" : NumberLong(10),
                "name" : "ABC",
                "severity" : "HIGH",
                "failures" : NumberLong(20),
                "description" : "Some description",
                "remediation" : "Some remedy"
            }, 
            {
                "miId" : NumberLong(18),
                "name" : "PQR",
                "severity" : "HIGH",
                "failures" : NumberLong(30),
                "description" : "Some description",
                "remediation" : "Some remedy"
            }
      }
}

我如何在MongoDB shell或Java中提出一个查询，该查询基于名称"进行groupby()并汇总所有失败"，结果还应包含具有最高名称的名称"的"uid" 失败". 结果应如下所示:

How do I come up with a query in MongoDB shell or Java which does groupby() based on "name" and sums up all "failures", the result should also contain "uid" of the "name" with the highest "failures". The result should look something like this:

{
{
    "_id" : ObjectId("508894efd4197aa2b9490741"),
    "name" : "ABC",
    "sum_total_of_failures" : 22,
    "uid" : 200
}

{
    "_id" : ObjectId("508894efd4197aa2b9490741"),
    "name" : "PQR",
    "sum_total_of_failures" : 33,
    "uid" : 200
}
}

任何帮助将不胜感激，我使用$ unwind编写了一个查询，因为"mi"文档存储在列表中，但是返回空结果. 查询如下:

Any help will be really appreciated, I wrote a query with $unwind as "mi" documents are stored in a list but it returned empty result. The query is as below:

    db.temp.aggregate(
{$unwind: "$mi"}, 
{$project: {mi : "$mi"}},
{$group: { _id: "$name",total: { $sum: "$failures" }}})

推荐答案

尝试以下查询:

db.collection.aggregate(
{$unwind : "$data.mi"},
{$sort : {"data.mi.failures" : -1}},
{$group : {_id : "$data.mi.name", 
           sum_total_of_failures : {$sum : "$data.mi.failures"}, 
           uid : {$first : "$uid"}}}
)

结果将类似于:

"result" : [
    {
        "_id" : "PQR",
        "sum_total_of_failures" : NumberLong(33),
        "uid" : 200
    },
    {
        "_id" : "ABC",
        "sum_total_of_failures" : NumberLong(22),
        "uid" : 200
    }
]

使用Java驱动程序，您可以执行以下操作:

With Java driver you can do it as follows :

    DBCollection coll = ...

    DBObject unwind = new BasicDBObject("$unwind", "$data.mi");
    DBObject sort = new BasicDBObject("$sort", new BasicDBObject("data.mi.failures", -1));

    DBObject groupObj = new BasicDBObject();
    groupObj.put("_id", "$data.mi.name");
    groupObj.put("sum_total_of_failures", new BasicDBObject("$sum", "$data.mi.failures"));
    groupObj.put("uid", new BasicDBObject("$first", "$uid"));

    DBObject group = new BasicDBObject("$group", groupObj);

    AggregationOutput output = coll.aggregate(unwind, sort, group);
    if (output != null) {
        for (DBObject result : output.results()) {
            String name = (String) result.get("_id");
            Long sumTotalOfFailures = (Long) result.get("sum_total_of_failures");
            Integer uid = (Integer) result.get("uid");
        }
    }

这篇关于MongoDB对嵌套文档列表的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！