I have data input that looks something similar to

{
    "someone": {
        "semesters":[
            {
                "semester": "Fall",
                "grades" : [
                    {
                        "exam" : "Midterm 1",
                        "sections" :[
                            {
                                "section" : "First Section",
                                "grade" : 30
                            },
                            {
                                "section" : "Second Section",
                                "grade" : 40
                            },
                            {
                                "section" : "Third Section",
                                "grade" : 50
                            }
                        ]
                    },
                    {
                        "exam" : "Midterm 2",
                        "sections" :[
                            {
                                "section" : "First Section",
                                "grade" : 12
                            },
                            {
                                "section" : "Second Section",
                                "grade" : 21
                            },
                            {
                                "section" : "Third Section",
                                "grade" : 56
                            }
                        ]
                    }
                ]
            }
        ]
    },
    "someone_else": {
        "semesters":[
            {
                "semester": "Fall",
                "grades" : [
                    {
                        "exam" : "Midterm 1",
                        "sections" :[
                            {
                                "section" : "First Section",
                                "grade" : 34
                            },
                            {
                                "section" : "Second Section",
                                "grade" : 3
                            },
                            {
                                "section" : "Third Section",
                                "grade" : 51
                            }
                        ]
                    },
                    {
                        "exam" : "Midterm 2",
                        "sections" :[
                            {
                                "section" : "First Section",
                                "grade" : 16
                            },
                            {
                                "section" : "Second Section",
                                "grade" : 80
                            },
                            {
                                "section" : "Third Section",
                                "grade" : 95
                            }
                        ]
                    }
                ]
            }
        ]
    }
}

I was wondering if there was a way to iterate through all the grades from multiple documents and make an output list that is sorted for all grades in the system. The data above should result in something like First Section:

{ name : someone_else, section: First Section, grade : 34 }, 
{ name : someone, section: First Section, grade : 30 }, 
{ name : someone_else, section: First Section, grade : 16 }, 
{ name : someone, section: First Section, grade : 12 }, 

I want to be able to change the section section and be able to find data for the highest grade in each specific sorted to lowest.

CodePudding user response：

This is actually quite simple, we just need to convert the grades object to an array using $objectToArray, then we can project and sort the into the required output, like so:

db.collection.aggregate([
  {
    $unwind: "$grades"
  },
  {
    $addFields: {
      grades: {
        "$objectToArray": "$grades"
      }
    }
  },
  {
    $unwind: "$grades"
  },
  {
    $project: {
      name: 1,
      grade: "$grades.v"
    }
  },
  {
    $sort: {
      grade: -1
    }
  }
])

Mongo Playground