count documents by specific nested fields values with aggregation-framework in MongoDB

Question

Here's my db.

{
"_id" : ObjectId("579cab6c6aba30f42a57a979"),
"iecode" : "P1111",
"country" : "India",
"totalTreatmentArms" : 3,
"treatmentArms" : [ 
    {
        "mechanismOrPkg" : "Mechanism",
        "mechanism" : "mechanism1"
    }, 
    {
        "mechanismOrPkg" : "Mechanism",
        "mechanism" : "mechanism2"
    }, 
    {
        "mechanismOrPkg" : "Package",
        "mechanism" : "mechanism1"
    }
]
}


{
"_id" : ObjectId("579cab7a6aba30f42a57a97a"),
"iecode" : "P1111",
"country" : "Canada",
"totalTreatmentArms" : 3,
"treatmentArms" : [ 
    {
        "mechanismOrPkg" : "Mechanism",
        "mechanism" : "mechanism1"
    }, 
    {
        "mechanismOrPkg" : "Mechanism",
        "mechanism" : "mechanism2"
    }, 
    {
        "mechanismOrPkg" : "Package",
        "mechanism" : ""
    }
]
}


{
"_id" : ObjectId("579cabac6aba30f42a57a97b"),
"iecode" : "P2222",
"country" : "India",
"totalTreatmentArms" : 1,
"treatmentArms" : [ 
    {
        "mechanismOrPkg" : "Package",
        "mechanism" : ""
    }
]
}


{
"_id" : ObjectId("579cabe76aba30f42a57a97c"),
"iecode" : "P3333",
"country" : "India",
"totalTreatmentArms" : 2,
"treatmentArms" : [ 
    {
        "mechanismOrPkg" : "Mechanism",
        "mechanism" : "mechanism1"
    }, 
    {
        "mechanismOrPkg" : "Package",
        "mechanism" : ""
    }
]
}

Please note that there are two records with iecode : P1111 and I want result distinguished on iecode so only one record will be considered(any one).

Now my requirement is I want count on field mechanismOrPkg. If it contains value Package then we will increment package with one. If the value is Mechanism then we will consider value of field mechanism and its respected values count will be considered.

So final result will be like this

{
"_id" : null,
"totalPackage" : 3,
"totalMechanism1" : 2,
"totalMechanism2" : 1
}

Please ask if it sounds confusing. Let me know even if this kind of aggregation is possible with query or I have to do server side filtering?

Thanks.

Edit

Possible values of mechanismOrPkg : Package or Mechanism

Possible values of mechanism : mechanism1 or mechanism2

you should also specify if all documents mechanism value where mechanismOrPkg is Mechanism can only have 2 values (mechanism1, mechanism2) as in yours example or amount of possible mechanisms is not restricted by yours application and it could be mechanism3, mechanism4, ... so the result document will be of different length depending on the particular collection state — Mr Tarsa
– Mr Tarsa, Commented Aug 1, 2016 at 8:16

chridam · Accepted Answer · 2016-08-01 07:48:07Z

Accomplishing the above would require using the $cond operator in the $sum accumulator operator. The $cond operator will evaluate a logical condition based on its first argument (if) and then returns the second argument where the evaluation is true (then) or the third argument where false (else). This converts the true/false logic into 1 and 0 numerical values that feed into $sum respectively. So for instance, when you want to aggregate the count for the "Package" value, the logic follows:

{
    "$sum": { 
        "$cond": [ { "$eq": [ "$treatmentArms.mechanismOrPkg", "Package" ] }, 1, 0 ]
    }
}

As a resulting pipeline, you need to run the aggregation operation

db.collection.aggregate([
    {
        "$group": {
            "_id": "$iecode",
            "treatmentArms": { "$first": "$treatmentArms" }
        }
    },
    { "$unwind": "$treatmentArms" },
    {
        "$group": {
            "_id": null,
            "totalPackage": { 
                "$sum": { 
                   "$cond": [ 
                       { "$eq": [ "$treatmentArms.mechanismOrPkg", "Package" ] }, 
                       1, 0
                    ] 
                }
            },
            "totalMechanism1":{
                "$sum": { 
                   "$cond": [ 
                        { 
                            "$and": [
                                { "$eq": [ "$treatmentArms.mechanismOrPkg", "Mechanism" ] },
                                { "$eq": [ "$treatmentArms.mechanism", "mechanism1" ] }
                            ]
                        }, 
                        1, 
                        0 ]
                }
            },
            "totalMechanism2": { 
                "$sum": { 
                   "$cond": [ 
                        { 
                            "$and": [
                                { "$eq": [ "$treatmentArms.mechanismOrPkg", "Mechanism" ] },
                                { "$eq": [ "$treatmentArms.mechanism", "mechanism2" ] }
                            ]
                        }, 
                        1, 
                        0 ]
                }
            }
        }
    }
])

Sample Output

{
    "_id" : null,
    "totalPackage" : 3,
    "totalMechanism1" : 2,
    "totalMechanism2" : 1
}

I am getting 0 records fetched. I think first grouping should be done on iecode.
Are you running the aggregate operation on the right collection? As you can see, the first grouping IS actually grouping by the iecode field.
sorry about that(grouping). Yes I am querying right collection still same result.
I checked it on the collection you provided and have an expected result, aggregate query is correct. It should be something with your collection or you have mistakenly copied the query from an answer. Check the query again and make sure that your collection is exactly the same as in your question.
@HalfBloodPrince Create another answer about your new issues, and I am sure that somebody will help you. Comments are not for long answers, but only for short clarification purposes. Also try to demonstrate there your attempts in solving the problem.

Collectives™ on Stack Overflow

count documents by specific nested fields values with aggregation-framework in MongoDB

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related