Sorting numbered sub-array with aggregation

Working with Data
1

I have many documents like these:

{
    "playerId": { "$oid": "609d0993906429612483cf72" },
    "roundNo": 1,
    "computedScore": 2,
    "countbackScores": [37, 24, 12, 4],
    "hcapPlaying": 10,
    "playerName": "Player 1"
}
{
    "playerId": { "$oid": "609d0993906429612483cf72" },
    "roundNo": 1,
    "computedScore": 1,
    "countbackScores": [40, 29, 18, 4],
    "hcapPlaying": 10,
    "playerName": "Player 2"
}
{
    "playerId": { "$oid": "609d0993906429612483cf72" },
    "roundNo": 1,
    "computedScore": 2,
    "countbackScores": [37, 20, 10, 1],
    "hcapPlaying": 10,
    "playerName": "Player 3"
}

The winning order would be:

  • Player 1 & 3 tied on computedScore
  • Player 1 wins as has higher countbackScore value for the 2nd array element (this should sort between the 4 numbers in the array in the left to right order per player)
  • Player 2 last on computedScore even though their countbackScores are higher

countbackScores, a number indexed array, so [1,2,3] with no names. I don’t know how to sort this in aggregation as I cannot name the element, which is what most documentation uses.

In PHP, I could loop through the MongoDB query result with a uasort function. Wondered if the aggregation framework could do the same thing within the DB?

2

Hi! @Dan_Burt .

Could you please post sample of out that your expect?

3

@HomAskIe Thanks for responding.

However, I am unsure as to what the output format would look like, as I don’t know how it would be processed.

Retaining the same document structure, just reordered according to the rules I outlined as I will access the various fields on the results I would like to display (HTML table, pre-formatted with PHP or JS).

Is that sufficient?

4

The following sort should work:

{ "computedScore":-1,
 "countbackScores.0":-1,
 "countbackScores.1":-1,
 "countbackScores.2":-1,
 "countbackScores.3":-1
}
5

Hello @Dan_Burt . As far as I understand in order to rank players computedScore is on highest priority and then countbackScores. Within that try this out see if it works for you: :point_right: Mongo playground

Basically before rank players. I sum their countbackScores and the sort by computedScore and then countbackScores this will solve the tie computed score problem.

Let me know if that helps and feel free to ask more questions :slight_smile:

6

The above does not support the use case:

It would work with the sample documents provided but the text description seems to indicate that player 1 would still win if the array of player 1 would be [37,24,1,0] and the array of player 2 would be [37,20,19,18] for example. Player 1 would have the value for the 2nd array element higher than the 2nd element from player 2. Using the sum would mean that player 2 is the winner, but it should be player 1.