MongoDB aggregate on Nested data

Question

I have nested data as below,

{
"_id" : ObjectId("5a30ee450889c5f0ebc21116"),
"academicyear" : "2017-18",
"fid" : "be02",
"fname" : "ABC",
"fdept" : "Comp",
"degree" : "BE",
"class" : "1",
"sem" : "8",
"dept" : "Comp",
"section" : "Theory",
"subname" : "BDA",
"fbValueList" : [ 
    {
        "_id" : ObjectId("5a30eecd3e3457056c93f7af"),
        "score" : 20,
        "rating" : "Fair"
    }, 
    {
        "_id" : ObjectId("5a30eefd3e3457056c93f7b0"),
        "score" : 10,
        "rating" : "Fair"
    }, 
    {
        "_id" : ObjectId("5a337e53341bf419040865c4"),
        "score" : 88,
        "rating" : "Excellent"
    }, 
    {
        "_id" : ObjectId("5a337ee2341bf419040865c7"),
        "score" : 75,
        "rating" : "Very Good"
    }, 
    {
        "_id" : ObjectId("5a3380b583dde50ddcea350e"),
        "score" : 72,
        "rating" : "Very Good"
    }
]
},
{
    "_id" : ObjectId("5a3764f1bc19b77dd9fd9a57"),
    "academicyear" : "2017-18",
    "fid" : "be02",
    "fname" : "ABC",
    "fdept" : "Comp",
    "degree" : "BE",
    "class" : "1",
    "sem" : "5",
    "dept" : "Comp",
    "section" : "Theory",
    "subname" : "BDA",
    "fbValueList" : [ 
        {
            "_id" : ObjectId("5a3764f1bc19b77dd9fd9a59"),
            "score" : 88,
            "rating" : "Excellent"
        }, 
        {
            "_id" : ObjectId("5a37667aee64bce1b14747d2"),
            "score" : 74,
            "rating" : "Good"
        }, 
        {
            "_id" : ObjectId("5a3766b3ee64bce1b14747dc"),
            "score" : 74,
            "rating" : "Good"
        }
    ]
}

We are trying to perform aggregation using this,

db.fbresults.aggregate([{$match:{academicyear:"2017-18",fdept:'Comp'}},{$group:{_id: {fname: "$fname", rating:"$fbValueList.rating"},count: {"$sum":1}}}])

and we get result like,

{ "_id" : { "fname" : "ABC", "rating" : [ "Fair","Fair","Excellent","Very Good", "Very Good",  "Excellent", "Good", "Good" ] }, "count" : 2 }

but we are expecting result like,

{ "_id" : { "fname" : "ABC", "rating_group" : [ 
        { 
            rating: "Excellent"
            count: 2 
        },
            { 
            rating: "Very Good"
            count: 2 
        },
            { 
            rating: "Good"
            count: 2 
        },
            { 
            rating: "Fair"
            count: 2 
        },

    ] }, "count" : 2 }

We want to get individual faculty group by their name and inside that group by their rating response and count of rating.

We have already tried this one but we did not the result. Mongodb Aggregate Nested Group

Which mongo version are you using?

Nandu Kalidindi
– Nandu Kalidindi

2017-12-18 09:28:42 +00:00
Commented Dec 18, 2017 at 9:28 — Nandu Kalidindi
– Nandu Kalidindi, Commented Dec 18, 2017 at 9:28

dnickless · Accepted Answer · 2017-12-18 10:01:48Z

3

This should get you going:

db.collection.aggregate([{
    $match: {
        academicyear: "2017-18",
        fdept:'Comp'
    }
}, {
    $unwind: "$fbValueList" // flatten the fbValueList array into multiple documents
}, {
    $group: {
        _id: {
            fname: "$fname",
            rating:"$fbValueList.rating"
        },
        count: {
            "$sum": 1 // this will give us the count per combination of fname and fbValueList.rating
        }
    }
}, {
    $group: {
        _id: "$_id.fname", // we only want one bucket per fname
        rating_group: {
            $push: { // we push the exact structure you were asking for
                rating: "$_id.rating",
                count: "$count"
            }
        },
        count: {
            $avg: "$count" // this will be the average across all entries in the fname bucket
        }
    }
}])

answered Dec 18, 2017 at 10:01

dnickless

10.9k1 gold badge22 silver badges35 bronze badges

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

1 Comment

Purvik Rana Over a year ago

thanks for your answer. It gives me the results that i want. thanks again. +1

Nandu Kalidindi · Accepted Answer · 2017-12-18 10:11:23Z

This is a long aggregation pipeline, there may be some aggregations that are un-necessary, so please check and discard whichever are irrelevant.

NOTE: This will only work with Mongo 3.4+.

You need to use $unwind and then $group and $push ratings with their counts.

matchAcademicYear = {
    $match: { 
        academicyear:"2017-18", fdept:'Comp' 
    }
}

groupByNameAndRating = {
    $group: {
        _id: { 
            fname: "$fname", rating:"$fbValueList.rating"
        },
        count: {
            "$sum":1
        }
    }
}

unwindRating = {
    $unwind: "$_id.rating"
}

addFullRating = {
    $addFields: {
        "_id.full_rating": "$count"
    }
}

replaceIdRoot = {
    $replaceRoot: {
        newRoot: "$_id"
    }
}


groupByRatingAndFname = {
    $group: {
        _id: {
            "rating": "$rating",
            "fname": "$fname"
        },
        count: {"$sum": 1},
        full_rating: {"$first": "$full_rating"}
    }
}

addFullRatingAndCount = {
    $addFields: {
        "_id.count": "$count",
        "_id.full_rating": "$full_count"
    }
}

groupByFname = {
    $group: {
        _id: "$fname",
        rating_group: { $push: {rating: "$rating", count: "$count"}},
        count: { $first: "$full_rating"}
    }    
}

db.fbresults.aggregate([
    matchAcademicYear, 
    groupByNameAndRating, 
    unwindRating, 
    addFullRating, 
    unwindRating, 
    replaceIdRoot, 
    groupByRatingAndFname, 
    addFullRatingAndCount, 
    replaceIdRoot, 
    groupByFname
])

Collectives™ on Stack Overflow

MongoDB aggregate on Nested data

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related