CODEWITHSUNDEEP
mongodbmongodb-querynosqlaggregation-framework
Dev
Dev

Reputation: 828

How to count embedded array object elements in mongoDB

{
    "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 }
     }}
 ])

Upvotes: 1

Views: 80

Answers (1)

mickl
mickl

Reputation: 49985

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

Upvotes: 3

Related Questions