Nested conditional MongoDB query

Question

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

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" }
         }
    }
] )

the result looks like this:

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

Philipp · Accepted Answer · 2015-08-29 00:15:57Z

1

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

answered Aug 29, 2015 at 0:15

Philipp

70.1k10 gold badges121 silver badges159 bronze badges

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

1 Comment

kahizer Over a year ago

i needed to make minor changes to get it to work i added { $unwind: "$Channels" } before project and changed "$Channels.Value" for "$Channels.value" on "documentSum" : { "$sum" : "$Channels.Value" }, "documentAvg" : { "$avg" : "$Channels.Value" },

Collectives™ on Stack Overflow

Nested conditional MongoDB query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related