Count elements of array that match a condition

Question

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?

@turivishal The expected result is just the number 3, or {"_id" : null, "count" : 3}. — Amir Shabani
– Amir Shabani, Commented May 22, 2021 at 11:56
Does this help you? stackoverflow.com/questions/35876100/… — Tushar Shahi
– Tushar Shahi, Commented May 22, 2021 at 12:01

turivishal · Accepted Answer · 2021-05-22 11:59:11Z

1

$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

answered May 22, 2021 at 11:59

turivishal

36.4k7 gold badges46 silver badges68 bronze badges

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

Collectives™ on Stack Overflow

Count elements of array that match a condition

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related