MongoDB count items in array

Question

Let's say we have a collection as follows

{ "_id" : 1, "item" : "ABC", "sizes": [ "S", "M", "L"] },
{ "_id" : 2, "item" : "EFG", "sizes": [ "S" ] },
{ "_id" : 3, "item" : "ABC", "sizes": [ "M" ] }

How can we list the items in the array (sizes) and the quantity of items associated to the sizes

For example

size 'S', item 'ABC' = 1
size 'S', item 'EFG' = 1
size 'M', item 'ABC' = 2
size 'L', item 'ABC' = 1

Note that size 'M' has to instances where item is 'ABC'.

I tried the following

db.collection.group(
{
    key: {'sizes': true}, 
    initial: {sum: 0}, 
    reduce: function(doc, prev) { prev.sum += 1}
});

but it doesn't unwind the array and I can not seem to able to add the Item as part of the group count

Any ideas?

Thanks

Sede · Accepted Answer · 2016-06-12 18:11:26Z

2

You need to $unwind the "sizes" array then $group by "item" and "sizes" and use the $sum to return the count.

db.collection.aggregate(
    [ 
        { "$unwind": "$sizes" }, 
        { "$group": { 
            "_id": { 
                "item": "$item", 
                "sizes": "$sizes" 
            }, 
            "count": { "$sum": 1 } 
        }}
    ]
)

which produces:

{ "_id" : { "item" : "EFG", "sizes" : "S" }, "count" : 1 }
{ "_id" : { "item" : "ABC", "sizes" : "L" }, "count" : 1 }
{ "_id" : { "item" : "ABC", "sizes" : "M" }, "count" : 2 }
{ "_id" : { "item" : "ABC", "sizes" : "S" }, "count" : 1 }

answered Jun 12, 2016 at 18:11

Sede

61.5k20 gold badges158 silver badges162 bronze badges

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

1 Comment

Selrac Over a year ago

This is fantastic, thank you. I was a bit puzzled on how to get the $unwind to work. This is great!!

Collectives™ on Stack Overflow

MongoDB count items in array

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