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

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 2

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.