MongoDB: Count of matching nested array elements

Question

I've got a simple parent child object stored as a document in MongoDB. Something simplistic like Order/OrderItems. (Order has an array of OrderItem)

What I'd like to do is query for a count of Order Items where they meet a set of criteria.

Example: In Order "999" find out how many order items had a quantity of 3.

db.collection.find( {OrderId:999, "OrderItems.QuantityOrdered":3} ).count();

The way this query works is it returns "1" because if it matches at least one OrderItem inside the array it will return the count of Orders matched.

How can I query for how many "OrderItems" matched?:

Seems to work for me. Unless I have the structure incorrectly. > db.posts.find() { "_id" : ObjectId("4c2e2c7bd39925311b1edae7"), "orderid" : 1, "orderitems" : { "quantityordered" : 3 } } { "_id" : ObjectId("4c2e2c8ad39925311b1edae8"), "orderid" : 1, "orderitems" : { "quantityordered" : 2 } } { "_id" : ObjectId("4c2e2c93d39925311b1edae9"), "orderid" : 1, "orderitems" : { "quantityordered" : 3 } } > — sdot257
– sdot257, Commented Jul 2, 2010 at 18:17

Kyle Banker · Accepted Answer · 2010-07-03 00:19:09Z

3

There's no direct way of return this sort of count with an embedded document. With this sort of ad-hoc count, your best bet is to return the document and do the count from the application.

If you want to perform this kind of count for a large number of orders, you could use map-reduce, which will output the results to a new collection.

answered Jul 3, 2010 at 0:19

Kyle Banker

4,36926 silver badges18 bronze badges

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

1 Comment

mikezter Over a year ago

I agree. However, if you need that kind of data often, the most promoted approach is to store the count in the document itself and update it accordingly on the application side.

ray · Accepted Answer · 2025-04-27 02:22:59Z

There are multiple ways to perform the expected behaviour:

using aggregation pipeline, $unwind and $count the result after $match

db.collection.aggregate([
  {
    "$match": {
      OrderId: 999
    }
  },
  {
    "$unwind": "$OrderItems"
  },
  {
    "$match": {
      "OrderItems.QuantityOrdered": 3
    }
  },
  {
    "$count": "count"
  }
])

Mongo Playground

using project option to $filter the result obtain the count directly with $size

db.collection.find({
  "OrderId": 999,
  "OrderItems.QuantityOrdered": 3
},
{
  "count": {
    "$reduce": {
      "input": "$OrderItems",
      "initialValue": 0,
      "in": {
        "$cond": {
          "if": {
            "$eq": [
              "$$this.QuantityOrdered",
              3
            ]
          },
          "then": {
            "$add": [
              1,
              "$$value"
            ]
          },
          "else": "$$value"
        }
      }
    }
  }
})

Mongo Playground

The best way in my opinion will be refactoring the collection to store OrderItems as individual documents. A simple count should be enough.

[
  {
    "OrderId": NumberLong(999),
    "QuantityOrdered": 5
  },
  {
    "OrderId": NumberLong(999),
    "QuantityOrdered": 1
  },
  {
    "OrderId": NumberLong(999),
    "QuantityOrdered": 3
  },
  {
    "OrderId": NumberLong(999),
    "QuantityOrdered": 4
  },
  {
    "OrderId": NumberLong(999),
    "QuantityOrdered": 3
  },
  {
    "OrderId": NumberLong(999),
    "QuantityOrdered": 2
  }
]

query:

db.collection.count({
  "OrderId": 999,
  "QuantityOrdered": 3
})

Mongo Playground

Collectives™ on Stack Overflow

MongoDB: Count of matching nested array elements

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related