MongoDB: Get count of Array

Question

Given these documents:

db.orders.insert( {OrderId:1, OrderItems: [{OrderItemId:1, Qty:1}, {OrderItemId:2, Qty:1} ]} );
db.orders.insert( {OrderId:2, OrderItems: [{OrderItemId:1, Qty:1}, {OrderItemId:2, Qty:2} ]} );

I'd like to get the count of all OrderItems where the Qty = 1 (expected result 3). This is how I think to write the query, but it returns 2 (1 for each Order document):

db.orders.find({"OrderItems.Qty":1}).count();

How can I query to find the count of all OrderItems where the Qty = 1?

Answer 1

Just to be clear for others reading this thread.

The OP's command db.orders.find({"OrderItems.Qty":1}).count(); basically counts the number of Documents where any order item has a quantity of 1.

However, the OP wants a count of all OrderItems where the quantity is one. The problem here is that we're not counting Documents. We're counting items within an array within a Document. Hence the need for javascript and some form of special reduce operation.

Answer 2

You could use JavaScript:

db.orders.group(
{
  key: null, 
  cond: {'OrderItems.Qty':1}, 
  initial: {count: 0}, 
  reduce: function(el, running)
  {                                                                                                       
    running.count += el.OrderItems.filter(function(el)                                                    
    {                                                                                                     
      return el.Qty == 1;                                                                                 
    }).length;                                                                                            
  }                                                                                                       
});

Answer 3

This should do it for you in the shell without JavaScript (so it'll be a lot quicker);

db.orders.aggregate([
  {$unwind:'$OrderItems'},
  {$match: {'OrderItems.Qty':1}},
  {$group : { 
      _id : "Qty1",
      sum: {$sum:1}
  }}
]);

Although it's unfortunate your data is structured like that if this is a common query. Having to do an $unwind is relatively expensive. It's a shame your order items aren't laid out as separate documents tagged with the order ID instead of orderID documents containing arrays of order items...in other words, the reverse of what you have. That would be much easier and more efficient to process.

Answer 4

db.orders.aggregate([
  {$unwind: '@OrderItems'},
  {$match : {'OrderItems.Qty':1}},
  {$group : { _id : null, 'countOfQty1':{$sum:'$OrderItems.Qty'} }}
]);