How to count embedded array object elements in mongoDB

Question

{
    "orderNo": "123",
    "bags": [{
            "type": "small",
            "products": [{
                    "id": "1",
                    "name": "ABC",
                    "returnable": true
                }, {
                    "id": "2",
                    "name": "XYZ"
                }
            ]
        },{
            "type": "big",
            "products": [{
                    "id": "3",
                    "name": "PQR",
                    "returnable": true
                }, {
                    "id": "4",
                    "name": "UVW"
                }
            ]
        }
    ]
}

I have orders collection where documents are in this format. I want to get a total count of products which has the returnable flag. e.g: for the above order the count should be 2. I am very new to MongoDB wanted to know how to write a query to find this out, I have tried few things but did not help: this is what I tried but not worked:

db.orders.aggregate([
     { "$unwind": "$bags" },
     { "$unwind": "$bags.products" },
     { "$unwind": "$bags.products.returnable" },
     
     { "$group": {
         "_id": "$bags.products.returnable",
         "count": { "$sum": 1 }
     }}
 ])

what is your expected result?

turivishal
– turivishal

2021-05-12 13:14:02 +00:00
Commented May 12, 2021 at 13:14 — turivishal
– turivishal, Commented May 12, 2021 at 13:14

mickl · Accepted Answer · 2021-05-12 13:17:37Z

3

For inner array you can use $filter to check returnable flag and $size to get number of such items. For the outer one you can take advantage of $reduce to sum the values from inner arrays:

db.collection.aggregate([
    {
        $project: {
            totalReturnable: {
                $reduce: {
                    input: "$bags",
                    initialValue: 0,
                    in: {
                        $add: [
                            "$$value",
                            {
                                $size: {
                                    $filter: {
                                        input: "$$this.products",
                                        as: "prod",
                                        cond: {
                                            $eq: [ "$$prod.returnable", true ]
                                        }
                                    }
                                }
                            ]
                        }
                    }
                }
            }
        }
    }
])

Mongo Playground

answered May 12, 2021 at 13:17

mickl

50.1k9 gold badges71 silver badges99 bronze badges

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

2 Comments

Dev Over a year ago

Thank you very much, 1 small question: what if I want the count based on the 'returnable' attributes availability not on its true/false value.

mickl Over a year ago

@Souma you can try this way: mongoplayground.net/p/-pe84bP6CfR

Collectives™ on Stack Overflow

How to count embedded array object elements in mongoDB

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related