嵌套条件MongoDB查询 [英] Nested conditional MongoDB query

问题描述

我很难用数组中某个项目的条件语句来运行一些嵌套查询。

Im having a hard time trying to run some nested queries with a conditional statement of an item inside an array.

这就是我的文档的样子。
我想获得一个摘要，例如 sum 和 average 和 alarmedCount （每次Channels.AlarmStatus == alarmed基于Channels.Id的每个 Channel的）。我得到了总和和平均值，但无法得到 alarmedCount

this is how my documents looks like. I would like to get a summary such as sum and average and alarmedCount (count every time Channels.AlarmStatus == "alarmed") of each "Channel" based on Channels.Id. I got sum and average to work but cant get the right query for alarmedCount

{
  "_id"       : "55df8e4cd8afa4ccer1915ee"
  "location"  : "1",
  "Channels" : [{
    "_id"          : "55df8e4cdsafa4cc0d1915r1",
    "ChannelId"    : 1,
    "Value"        : 14,
    "AlarmStatus"  : "normal"
    },
    {
    "_id"          : "55df8e4cdsafa4cc0d1915r9",
    "ChannelId"    : 2,
    "Value"        : 20,
    "AlarmStatus"  : "alarmed"
    },
    {
    "_id"          : "55df8e4cdsafa4cc0d1915re",
    "ChannelId"    : 3,
    "Value"        : 10,
    "AlarmStatus"  : "alarmed"},
  ]
}
    {
  "_id"       : "55df8e4cd8afa4ccer1915e0"
  "location"  : "1",
  "Channels" : [{
    "_id"          : "55df8e4cdsafa4cc0d19159",
    "ChannelId"    : 1,
    "Value"        : 50,
    "AlarmStatus"  : "normal"
    },
    {
    "_id"          : "55df8e4cdsafa4cc0d1915re",
    "ChannelId"    : 2,
    "Value"        : 16,
    "AlarmStatus"  : "normal"
    },
    {
    "_id"          : "55df8e4cdsafa4cc0d1915g7",
    "ChannelId"    : 3,
    "Value"        : 9,
    "AlarmStatus"  : "alarmed"},
  ]
}

我可以使用它们汇总并显示一些计算结果

I got it to work to group them and show some calculations using this aggregate

db.records.aggregate( [ 
    { 
        "$unwind" : "$Channels"
    }, 
    {
        "$group" : {
            "_id" : "$Channels.Id",
            "documentSum" : { "$sum" : "$Channels.Value" },
            "documentAvg" : { "$avg" : "$Channels.Value" }
         }
    }
] )

结果如下：

{
    "result" : [ 
        {
            "_id" : 1,
            "documentSum" : 64,
            "documentAvg" : 32
        },
        {
            "_id" : 2,
            "documentSum" : 36,
            "documentAvg" : 18
        },
        {
            "_id" : 3,
            "documentSum" : 19,
            "documentAvg" : 9.5
        }, 
    ],
    "ok" : 1.0000000000000000
}

我想得到这种类型的结果

I would like to get this type of result

{
        "result" : [ 
            {
                "_id" : 1,
                "documentSum" : 64,
                "documentAvg" : 32,
                "AlarmedCount" : 0
            },
            {
                "_id" : 2,
                "documentSum" : 36,
                "documentAvg" : 18,
                "AlarmedCount" : 1
            },
            {
                "_id" : 3,
                "documentSum"  : 19,
                "documentAvg"  : 9.5,
                "AlarmedCount" : 2
            } 
        ],
        "ok" : 1.0000000000000000
    }

推荐答案

在分组步骤之前使用项目步骤将字段 AlarmedStatus 转换为 1 或 0 取决于其值：

Use a project-step before your group-step to convert the field AlarmedStatus to 1 or 0 depending on its value:

$project: {
     "Channels.value":"$Channels.Value",
     "Channels.AlarmCount":{ $cond: { 
           if: { $eq: ["$Channels.AlarmedStatus", "alarmed"] }, 
           then: 1, 
           else: 0 } 
     }
}

然后对新创建的字段求和以得到合计计数：

Then sum the newly created field to get the aggregated count:

$group : {
     "_id" : "$Channels.Id",
     "documentSum" : { "$sum" : "$Channels.Value" },
     "documentAvg" : { "$avg" : "$Channels.Value" },
     "AlarmCount"  : { "$sum" : "$Channels.AlarmCount" }
 }

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