I have a highly nested MongoDB set of objects and I want to sort subdocuments according to the result of sum of their putts for each hole for example :

[
{
            "_id": "61cab7361f1c58e1555b1cbb",
            "userId": "user1",
            "matchType": "tournament",
            "scoreHoles": [
                {
                    "hole": "hole1"              
                    "put": 2,                
                    "_id": "61cab86e0f765cd9428de547"
                },
                {
                    "holeId":"hole2"
                    "put": 3,
                    "_id": "61cab86e0f765cd9428de548"
                }
            ],
        },
        {
            "_id": "61cab6361f1c58e1555b1c93",
            "userId": "user 2",
            "matchType": "tournament",
            "scoreHoles": [
                {
                    "holeId": "hole1",
                    "put": 4,
                },
                {
                    "holeId": "hole2",
                    "put": 4,
                }
            ],
        }
]

I want to sort as descending point according to the result of sum of their scoreHole.puts the expected output is

[
{
    "userId" : xxx,
    "scoreHoles": [
                {
                    "hole": "hole1",
                    "put": 4,
                    "_id": "61cab86e0f765cd9428de547"
                },
                {
                    "hole":"hole2",
                    "put": 4,
                }
            ],
   "totalPuts" : 8
},
    "userId" : xxx,
    "scoreHoles": [
                {
                    "holeId": "hole1",
                    "put": 2,
                },
                {
                    "holeId": "hole2"
                    "put": 3,
                }
            ],
   "totalPuts" : 5
]

Any Idea ?

CodePudding user response：

1. $project - Decorate output documents. With $reduce, to create totalPuts by summing the put value from each object in scoreHoles array.
2. $sort - Sort totalPuts by descending.

db.collection.aggregate([
  {
    $project: {
      _id: 0,
      userId: 1,
      scoreHoles: 1,
      totalPuts: {
        "$reduce": {
          "input": "$scoreHoles",
          "initialValue": 0,
          "in": {
            $sum: [
              "$$value",
              "$$this.put"
            ]
          }
        }
      }
    }
  },
  {
    "$sort": {
      totalPuts: -1
    }
  }
])

Sample Demo on Mongo Playground