CODEWITHSUNDEEP
mongodb
Amir Shabani
Amir Shabani

Reputation: 4177

Count elements of array that match a condition

This is the simplified version of a collection I'm working on:

> use tdb
> db.tcol.insertMany([{"pid": 1, "type": "simple", "variations": []}, {"pid": 2, "type": "simple", "variations": []}, {"pid": 3, "type": "variable", "variations": [{"vid": 1, "instock": false}, {"vid": 2, "instock": true}, {"vid": 3, "instock": true}]}, {"pid": 4, "type": "variable", "variations": [{"vid": 1, "instock": false}, {"vid": 2, "instock": false}]}, {"pid": 5, "type": "variable", "variations": [{"vid": 1, "instock": true}]}])
> db.tcol.find({}, {"_id": 0}).pretty()
{ "pid" : 1, "type" : "simple", "variations" : [ ] }
{ "pid" : 2, "type" : "simple", "variations" : [ ] }
{
        "pid" : 3,
        "type" : "variable",
        "variations" : [
                {
                        "vid" : 1,
                        "instock" : false
                },
                {
                        "vid" : 2,
                        "instock" : true
                },
                {
                        "vid" : 3,
                        "instock" : true
                }
        ]
}
{
        "pid" : 4,
        "type" : "variable",
        "variations" : [
                {
                        "vid" : 1,
                        "instock" : false
                },
                {
                        "vid" : 2,
                        "instock" : false
                }
        ]
}
{
        "pid" : 5,
        "type" : "variable",
        "variations" : [
                {
                        "vid" : 1,
                        "instock" : true
                }
        ]
}

And I'm trying to count the number of elements in variations arrays that are in stock ("instock": true), which in this example would be 3, {"pid": 2, "vid": 2}, {"pid": 2, "vid": 3}, and {"pid": 5, "vid": 1}.

Also, only records with "type": "variable" have variations, so I have to filter on that as well.

In order to count, I saw that I can use aggregation and dot notation to return only those records that have at least one document with "instock": true in variations array:

> db.tcol.aggregate([{"$match": {"type": "variable", "variations.instock": true}}, {"$project": {"_id": 0}}])
{ "pid" : 3, "type" : "variable", "variations" : [ { "vid" : 1, "instock" : false }, { "vid" : 2, "instock" : true }, { "vid" : 3, "instock" : true } ] }
{ "pid" : 5, "type" : "variable", "variations" : [ { "vid" : 1, "instock" : true } ] }

Now if use $group like this, I will get 2, which is just records that have at least one document with instock of true in variations array.

> db.tcol.aggregate([{"$match": {"type": "variable", "variations.instock": true}}, {"$group": {"_id": null, "count": {"$sum": 1}}}])
{ "_id" : null, "count" : 2 }

But I'm trying to count all elements of this array that are in stock and return the sum for all records of tcol. How can I do that?

Upvotes: 1

Views: 931

Answers (1)

turivishal
turivishal

Reputation: 36104

  • $match your conditions
  • $filter to iterate loop pf variations and filter by instock
  • $size to get total elements in above filtered result
  • $group by null and sum total elements from above size
db.tcol.aggregate([
  {
    $match: {
      "type": "variable",
      "variations.instock": true
    }
  },
  {
    $group: {
      _id: null,
      total: {
        $sum: {
          $size: {
            $filter: {
              input: "$variations",
              cond: { $eq: ["$$this.instock", true] }
            }
          }
        }
      }
    }
  }
])

Playground

Upvotes: 1

Related Questions