Add ES query to fetch top risk scores grouped by project.

On the group risk score tab, we need top n risk scores grouped by project (currently n=96, a randomly chosen number for now). Which would look something like this -

We already have the group risk score query which fetches the risk score for the whole project. The query for that looks something like this -

ES query to fetch risk scores for a group

More details about the query and the reasoning in this thread - #571079 (comment 2816510162)

GET gitlab-development-vulnerabilities/_search
{
  "size": 0,
  "track_total_hits": true,
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "should": [
              {
                "prefix": {
                  "traversal_ids": {
                    "_name": "namespace:ancestry_filter:descendants",
                    "value": "24-"
                  }
                }
              }
            ],
            "minimum_should_match": 1
          }
        },
        {
          "terms": {
            "state": [
              1,
              4
            ]
          }
        }
      ]
    }
  },
  "aggs": {
    "risk_scores_sum": {
      "sum": {
        "field": "risk_score"
      }
    },
    "created_at_sum": {
      "sum": {
        "field": "created_at"
      }
    }
  }
}

Now we need to fetch scores grouped by project in the group. But the tricky part is fetching the top n risk scores. We can do a nested aggregation to fetch the risk scores by projects easily -

ES query for risk Scores grouped by project in a group

GET gitlab-development-vulnerabilities/_search
{
  "size": 0,
  "track_total_hits": true,
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "should": [
              {
                "prefix": {
                  "traversal_ids": {
                    "_name": "namespace:ancestry_filter:descendants",
                    "value": "24-"
                  }
                }
              }
            ],
            "minimum_should_match": 1
          }
        },
        {
          "terms": {
            "state": [
              1,
              4
            ]
          }
        }
      ]
    }
  },
  "aggs": {
    "by_project": {
      "terms": {
        "field": "project_id",
        "size": 5
      },
      "aggs": {
        "risk_scores_sum": {
          "sum": {
            "field": "risk_score"
          }
        },
        "created_at_sum": {
          "sum": {
            "field": "created_at"
          }
        }
      }
    }
  }
}

Response

{
  "took": 6,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 1736,
      "relation": "eq"
    },
    "max_score": null,
    "hits": []
  },
  "aggregations": {
    "by_project": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 61,
      "buckets": [
        {
          "key": 43,
          "doc_count": 1100,
          "risk_scores_sum": {
            "value": 0
          },
          "created_at_sum": {
            "value": 1920062013246300,
            "value_as_string": "+62814-05-05T23:14:06.300Z"
          }
        },
        {
          "key": 45,
          "doc_count": 152,
          "risk_scores_sum": {
            "value": 0
          },
          "created_at_sum": {
            "value": 266864797996363,
            "value_as_string": "+10426-08-10T22:33:16.363Z"
          }
        },
        {
          "key": 38,
          "doc_count": 141,
          "risk_scores_sum": {
            "value": 0
          },
          "created_at_sum": {
            "value": 246079801624483,
            "value_as_string": "9767-12-16T20:27:04.483Z"
          }
        },
        {
          "key": 39,
          "doc_count": 141,
          "risk_scores_sum": {
            "value": 0
          },
          "created_at_sum": {
            "value": 246079984513236,
            "value_as_string": "9767-12-18T23:15:13.236Z"
          }
        },
        {
          "key": 41,
          "doc_count": 141,
          "risk_scores_sum": {
            "value": 0
          },
          "created_at_sum": {
            "value": 246116138717842,
            "value_as_string": "9769-02-09T10:05:17.842Z"
          }
        }
      ]
    }
  }
}

Implementation plan

Modify the aggregation query and fetch risk scores sum and created_at sum grouped by projects.
Set the Above aggregation bucket size to 65_536 - the search.max_buckets value on ES.
Introduce FF to fallback to not using the nested aggregation in case we see any issues.

For alternatives that were discussed before, expand this

For nested aggregations Elasticsearch fetches top n buckets based on the count of records in the bucket¹, in our case it would fetch top n buckets based on the count of vulnerabilities in the project. But this might not be the ideal ordering or rather the ordering we want since there could be a project which has fewer vulnerabilities but higher risk score (for e.g a project with higher critical vulnerabilities). So we would need to figure out a way to achieve our goal.

Here are some ideas which I could think of -

Use the nested aggregation as above (i.e order by count of records), but on the UI we could show that these are the top 96 projects "by vulnerabilities count" sorted by descending order of risk scores (need Product's opinions on this)
Set size param¹ or shard_size param² to a sufficiently large number (say 1k or 10k) and return only top n projects by risk score. This would not be ideal for very large groups (with >1k projects), but would work perfectly well for small to medium groups.
Aggregate by project and order by sum of vulnerability risk scores (risk_scores_sum in the query above). This would be an approximate ordering since this ordering does not consider the age modifier part of the risk score. But the caveat here is that this might not be very accurate³ on top of it being already being approximate as mentioned just before.

Edited Nov 04, 2025 by Rushik Subba