In order to be able to use an aggregation for sorting the terms aggregation it must currently be a numeric metric aggregation. Pipeline Aggregations are a different family of aggregations than the Metric Aggregations and cannot be used for sorting. You can't sort using a Pipeline aggregation because they are only executed in the reduce phase on the coordinating node and so you do not have the information on the shard in order to be able to sort the shards buckets to pick the top N to send to the coordinating node.
The Scripted Metric Aggregation is part of the Metric Aggregation family but is not a Numeric Metric Aggregation since it returns an arbitrary object. However, there is an issue open in the Elasticsearch repo to add the ability to sort by an attribute of the Scripted Metric Aggregation: https://github.com/elastic/elasticsearch/issues/8486