Count Array Elements Matching Condition

Question

I have a collection in MongoDB and their objects look like this:

Object 1

{
    "_id" : ObjectId("5afde62a91952a2980a1b751"),   
    "notificationName" : "Teste Agendamento Pontual",
    "messages" : [ 
        {
            "timestamp" : ISODate("2018-05-17T20:29:33.045Z"),            
            "message" : "Teste Agendamento Pontual"
        }, 
        {
            "timestamp" : ISODate("2018-05-17T20:29:33.051Z"),            
            "message" : "Teste"
        }, 
        {
            "timestamp" : ISODate("2018-05-17T20:29:44.680Z"),            
            "message" : "OK"
        }        
    ]
}

Object 2

{
    "_id" : ObjectId("5afde62a9194322980a1b751"),   
    "notificationName" : "Teste Agendamento Pontual",
    "messages" : [ 
        {
            "timestamp" : ISODate("2018-05-17T20:29:33.045Z"),            
            "message" : "Teste Agendamento Pontual"
        }, 
        {
            "timestamp" : ISODate("2018-05-17T20:29:33.051Z"),            
            "message" : "NOT OK"
        }, 
        {
            "timestamp" : ISODate("2018-05-17T20:29:44.680Z"),            
            "message" : "asdsadasd"
        }        
    ]
}

...

I'm trying to get a result grouped by notificationName with a count of objects with OK messages and other count with objects with NOT OK . I think I need to make a $cond in $group to check message property, but I'm not sure about that. And not sure if thats the best way to achieve this.

Neil Lunn · Accepted Answer · 2018-06-13 00:06:20Z

If you want matching array elements then you want $filter instead and count them using $size:

db.collection.aggregate([
  { "$group": {
    "_id": "$notificationName",
    "countOk": {
      "$sum": {
        "$size": {
          "$filter": {
            "input": "$messages.message",
            "cond": { "$eq": [ "$$this", "OK" ] }
          }
        } 
      }
    }
  }}
])

The $filter has it's own cond argument which is a logical condition to return a boolean value determining whether the array element matches that condition or not and can be returned. Since this would only then return an array of values from message where the value is "OK" using the $eq comparison operator to test, then you "count" the resulting array members using $size.

Because you are "grouping" you use $group as the stage to do this, and because you are "accumulating" you use the $sum operator to "add up" all the returned $size results from each document sharing the same grouping key

Collectives™ on Stack Overflow

Count Array Elements Matching Condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related