MongoDB count if exists and grouped by distinct array values

Question

I have a collection looking like this:

{
    "_id" : 1,
    "category" : [
         {
           "name": "category1"
         },
         {
           "name": "category2"
         }
    ],
    "event": [
               {
                 type: "house"
               },
               {
                 type: "mouse"
               }
             ]
},

{
    "_id" : 2,
    "category" : [
         {
           "name": "category2"
         },
         {
           "name": "category3"
         }
    ]
},

{
    "_id" : 3,
    "category" : [
         {
           "name": "category3"
         },
         {
           "name": "category1"
         }
    ],
        "event": [
           {
             type: "mouse"
           }
         ]
}

I need to do a group by distinct category name types and count if an event exists to get a document like:

{ "category1": total_count: 2,
  "category2": total_count: 0,
  "category3": total_count: 1}

Any help would be highly appreciated.

yes, but without success.. it would work if category isn't an array, but i don't know how to "explode" the array first and then group it by distinct values of them — Codutie
– Codutie, Commented Jul 21, 2017 at 8:15

adi · Accepted Answer · 2017-07-21 12:47:14Z

2

Try this, it works for me:

db.getCollection('coll').aggregate([
{$unwind: "$category"},
{$group :{
    _id : "$category.name",   
    total_count : {$sum : 1}
 }
}])

answered Jul 21, 2017 at 12:47

adi

3042 silver badges11 bronze badges

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

2 Comments

Codutie Over a year ago

unwind! that's it

adi Over a year ago

@Codutie yep :)

Collectives™ on Stack Overflow

MongoDB count if exists and grouped by distinct array values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related