Ranking data efficiently

Working with Data
, ,
1

I have the following code from Compass on an pipeline aggregation that I’m looking to create. I would like to be able to rank by goals scored, but want to sort the data so that the best possible stats appear in a rank (1 being the lowest and somewhere near 100 being the highest) for players. The issue is, with the code below, the $setWindowFields stage needs the sortby command in order to get ranking and it basically throws away my previous sorting for the final 100 documents. Is there a way to get the ranking I need by more than 1 field?


[
  {
    $match:
      /**
       * query: The query in MQL.
       */
      {
        League: "English Premier League",
        Pos: {
          $in: ["MF", "MFFW"],
        },
        Min: {
          $gt: 0,
        },
        Gls: {
          $gt: 0,
        },
      },
  },
  {
    $sort:
      /**
       * Provide any number of field/order pairs.
       */
      {
        Gls: -1,
      },
  },
  {
    $limit:
      /**
       * Provide the number of documents to limit.
       */
      100,
  },
  {
    $sort:
      /**
       * Provide any number of field/order pairs.
       */
      {
        Gls: 1,
        Gls_90: 1,
        Ast: 1,
      },
  },
  {
    $setWindowFields:
      /**
       * partitionBy: partitioning of data.
       * sortBy: fields to sort by.
       * output: {
       *   path: {
       *     function: The window function to compute over the given window.
       *     window: {
       *       documents: A number of documents before and after the current document.
       *       range: A range of possible values around the value in the current document's sortBy field.
       *       unit: Specifies the units for the window bounds.
       *     }
       *   }
       * }
       */

      {
        sortBy: {
          Gls: 1,
        },
        output: {
          rank: {
            $documentNumber: {},
          },
        },
      },
  },
  {
    $project:
      /**
       * specifications: The fields to
       *   include or exclude.
       */
      {
        Player: "$Player",
        Club: "$Club",
        League: "$League",
        Season: "$Season",
        Gls_score: "$rank",
      },
  },
  {
    $out:
      /**
       * Provide the name of the output collection.
       */
      "Rankings",
  },
]
3

Hi @Steve_Gilliard and welcome to MongoDB community forums!!

As mentioned in the MongoDB documentations for $setWindowFields, multiple fields could be used for the sortBy attribute, however, to understand your concern better and suggest you with another aggregation pipeline or help you with a possible solution, it important for us to understand a few things. It would be great if you could share information like:

  1. Some sample documents from the collection on which the aggregation is being run against.
  2. The expected response from the collection created.
  3. Index defined for any fields and the version being used.

Regards
Aasawari

4
  1. Here’s a sample of the documents:
[{
  "Player": "Player 1",
  "Pos": "FW",
  "Gls": 29,
  "Ast": 5,
  "Gls_90": {
    "$numberDecimal": "0.93"
  },
  "Club": "Club 1",
  "Season": "22/23"
},
{
  "Player": "Player 2",
  "Pos": "FW",
  "Gls": 3,
  "Ast": 4,
  "Gls_90": {
    "$numberDecimal": "0.23"
  },
  "Club": "Club 2",
  "Season": "22/23"
},
{
  "Player": "Player 3",
  "Pos": "FW",
  "Gls": 25,
  "Ast": 2,
  "Gls_90": {
    "$numberDecimal": "0.85"
  },
  "Club": "Club 3",
  "Season": "22/23"
},
{
  "Player": "Player 4",
  "Pos": "FW",
  "Gls": 18,
  "Ast": 7,
  "Gls_90": {
    "$numberDecimal": ".70"
  },
  "Club": "Club 4",
  "Season": "22/23"
},
]

  1. I’m looking to sort on Gls, then on Ast and finally Gls_90. This would give me the order that I’d like to have the data sorted by that would give me the best ranking results.

  2. Currently, I have this indexed on Player and Club. The version of mongodb I’m using is : “version”: “6.0.6”

Thank you for any help you can provide,

Steve

5

Also, for the desired results, I’m looking for something similar to:

[{
  "Player": "Player 1",
  "Pos": "FW",
  "Gls": 29,
  "Ast": 5,
  "Gls_90": {
    "$numberDecimal": "0.93"
  },
  "Club": "Club 1",
  "Season": "22/23",
  "Rank": 1,
},
{
  "Player": "Player 2",
  "Pos": "FW",
  "Gls": 3,
  "Ast": 4,
  "Gls_90": {
    "$numberDecimal": "0.23"
  },
  "Club": "Club 2",
  "Season": "22/23",
  "Rank": 4,
},
{
  "Player": "Player 3",
  "Pos": "FW",
  "Gls": 25,
  "Ast": 2,
  "Gls_90": {
    "$numberDecimal": "0.85"
  },
  "Club": "Club 3",
  "Season": "22/23",
  "Rank": 2,
},
{
  "Player": "Player 4",
  "Pos": "FW",
  "Gls": 18,
  "Ast": 7,
  "Gls_90": {
    "$numberDecimal": ".70"
  },
  "Club": "Club 4",
  "Season": "22/23",
  "Rank": 3,
},
]
6

I figured this out to get my desired solution:

Instead of sortby being 1 field, I combined fields that gave me the number I needed to rank the players. There are numerous ways to do this, but I added the Gls, Ast and Gls_90, to get the order I needed them by. From this, I was able to create a combinedRankField of the 3 and use that as the sortby field. $addfields:

{
  combinedRankingField: {
    $add: ["$Gls", "$Ast", "$G+A_90"],
  },
}

$setWindowFields:

{
  sortBy: {
    combinedRankingField: 1,
  },
  output: {
    rank: {
      $documentNumber: {},
    },
  },
}

$project:

{
  Player: "$Player",
  Club: "$Club",
  Pos: "$Pos",
  League: "$League",
  Season: "$Season",
  Gls: "$Gls",
  Gls_90: "$Gls_90",
  Ast: "$Ast",
  "G+A_90": "$G+A_90",
  Score: "$combinedRankingField",
  Scoring_Rank: "$rank",
}
7

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.