CODEWITHSUNDEEP
mongodbaverageaggregation-framework
user602599
user602599

Reputation: 661

MongoDB Aggregate group by multiple fields and get average

How to first group the following documents by "patient_group" and then by "neuro-time-point" to find the average of "hricph"?

{ "cases" : { "neuro" : { "neuro-time-point" : "0-12", "hricph" : 1 }, "patient_group" : "HSD" } }
{ "cases" : { "neuro" : { "neuro-time-point" : "12-24", "hricph" : 2 }, "patient_group" : "HSD" } }
{ "cases" : { "neuro" : { "neuro-time-point" : "24-36", "hricph" : 3 }, "patient_group" : "HSD" } }


{ "cases" : { "neuro" : { "neuro-time-point" : "0-12", "hricph" : 1 }, "patient_group" : "HSD" } }
{ "cases" : { "neuro" : { "neuro-time-point" : "24-36", "hricph" : 5 }, "patient_group" : "HSD" } }
{ "cases" : { "neuro" : { "neuro-time-point" : "36-48", "hricph" : 5 }, "patient_group" : "HSD" } }

Desired result (or something close to this, in a format to render a multiple line chart for different patient groups):

{ "patient_group" : "HSD", 
    "avg_hricph_val" : 
        {   
            "neuro_time_point" : "0-12",
            "hricph" : 1
        },
        {   
            "neuro_time_point" : "12-24",
            "hricph" : 2
        },
        {   
            "neuro_time_point" : "24-36",
            "hricph" : 4
        },
        {   
            "neuro_time_point" : "36-48",
            "hricph" : 5
        }
}

Here's what I have tried:

db.test_collection.aggregate([  { "$group": { 
    "_id": { 
            "patient_group": "$cases.patient_group", 
            "neuro_time_point" : "$cases.neuro.neuro-time-point", 
            "hricph" : "$cases.neuro.hricph"         
            },         
            "avg_hricph_val": { "$avg": 1 }     
        }} 
])

Upvotes: 0

Views: 726

Answers (1)

Sede
Sede

Reputation: 61225

What about this:

db.test_collection.aggregate(
    [
        { 
            "$group": { 
                          "_id": { "id": "$cases.patient_group", "neuro_time_point": "$cases.neuro.neuro-time-point" },
                          "hricph": { "$avg": "$cases.neuro.hricph" }
                      }
             },
        { 
            "$group": {
                          "_id": null, "patient_group": { "$first": "$_id.id" }, 
                          "avg_hricph_val": { "$addToSet": { "neuro_time_point": "$_id.neuro_time_point", "hricph": "$hricph" }}
                       }
        },
        { 
            "$project": { "patient_group": 1, "_id": 0, "avg_hricph_val": 1 }
        }
    ]
)

Result

{
        "patient_group" : "HSD",
        "avg_hricph_val" : [
                {
                        "neuro_time_point" : "0-12",
                        "hricph" : 1
                },
                {
                        "neuro_time_point" : "12-24",
                        "hricph" : 2
                },
                {
                        "neuro_time_point" : "24-36",
                        "hricph" : 4
                },
                {
                        "neuro_time_point" : "36-48",
                        "hricph" : 5
                }
        ]
}

Upvotes: 1

Related Questions